Drill 5 of 8 in Fintech questions
0

Interview Question Bank · generate_series · LEFT JOIN · signed sums

5. Daily net cash flow, quiet days included

MediumFintechQuestion #104

Concept: generate_series · LEFT JOIN · signed sumsshow

A daily report needs a row for every day, but GROUP BY only returns days that have data. Build the calendar with generate_series(start, end, INTERVAL '1 day'), LEFT JOIN the activity onto it, and wrap sums in COALESCE(..., 0), because SUM over no rows is NULL. When the direction is a separate column, turn it into a sign with CASE before summing.

Example (different data):

SELECT d::date AS day,
  COALESCE(SUM(CASE WHEN e.kind = 'checkout' THEN 1 ELSE -1 END), 0) AS net_loans
FROM generate_series('2024-05-01'::date, '2024-05-07'::date, INTERVAL '1 day') AS d
LEFT JOIN library_events e ON e.happened_at::date = d::date
GROUP BY d
ORDER BY d;

Your task

Northpine Payments' treasury desk wants a daily cash report for the first ten days of March 2025, with a line for every day, including days when no money moved.

For every day from 2025-03-01 to 2025-03-10, return day (a DATE), cash_in (total amount with direction 'in'), cash_out (total amount with direction 'out') and net_flow (cash_in minus cash_out) from fin_cash_movements, with 0 on days without movements. moved_at is a timestamp; a movement belongs to the calendar day it happened on. Oldest first.

Starting Postgres in your browser…

Checking your access…