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…