Write a running total of revenue per customer by day.

“I’ll assume orders(order_id, customer_id, paid_at, amount_cents), revenue as orders with a paid_at, counted on the day they were paid, in integer cents, and days in UTC. In Postgres, paid_at::date on a timestamptz uses the session’s time zone, so I name the zone. For a business day in New York it’s AT TIME ZONE 'America/New_York'. First the grain, then the window.”

WITH daily AS (
  SELECT
    customer_id,
    (paid_at AT TIME ZONE 'UTC')::date AS day,
    SUM(COALESCE(amount_cents, 0))     AS revenue_cents
  FROM orders
  WHERE paid_at IS NOT NULL
  GROUP BY customer_id, day
)
SELECT
  customer_id,
  day,
  revenue_cents,
  SUM(revenue_cents) OVER (
    PARTITION BY customer_id
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue_cents
FROM daily
ORDER BY customer_id, day;

“The partition restarts the total for each customer, the order accumulates in time, and the frame is explicit. After the GROUP BY, each customer has one row per day, so ROWS and the default RANGE agree here. On raw orders they wouldn’t, and neither would give one row per day, which is why I aggregate first. SQLite has had window functions since version 3.25.0; there the day is DATE(paid_at), which reads stored timestamps as UTC, and the rest runs unchanged. The SQLite documentation on window frames spells out the default.”

“If they want every calendar day, including days with nothing, I build a date spine from each customer’s first day and fill gaps with zero:”

WITH daily AS (
  -- same daily step as above
),
grid AS (
  SELECT f.customer_id, g.day::date AS day
  FROM (SELECT customer_id, MIN(day) AS first_day FROM daily GROUP BY customer_id) AS f
  CROSS JOIN LATERAL generate_series(
    f.first_day, (SELECT MAX(day) FROM daily), interval '1 day'
  ) AS g(day)
)
SELECT
  g.customer_id,
  g.day,
  COALESCE(d.revenue_cents, 0) AS revenue_cents,
  SUM(COALESCE(d.revenue_cents, 0)) OVER (
    PARTITION BY g.customer_id
    ORDER BY g.day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue_cents
FROM grid AS g
LEFT JOIN daily AS d
  ON d.customer_id = g.customer_id AND d.day = g.day
ORDER BY g.customer_id, g.day;

“In SQLite, whose core library has no generate_series (only the command-line shell adds one), a recursive CTE builds the spine.”

“Refunds: I’d UNION ALL them into the daily step as negative amounts on the refund’s date, so the running total can go down on the day money went back. That only works if the orders side counts every order that was ever paid, which is why I filter on paid_at IS NOT NULL rather than a current status. Otherwise a refunded order drops out of its original day and the refund subtracts it again, and last month’s totals change under the finance team.”

“Then I’d check the edge cases I listed. SUM skips nulls, but a sum over only nulls is NULL, so a customer whose first day has only null amounts would start with a null running total. That’s why the daily step sums COALESCE(amount_cents, 0), and I’d still ask why a paid order has no amount. For all customers this is a full scan whatever the indexes. If the dashboard asks for one customer, an index on (customer_id, paid_at) turns it into a range scan. Showing only the last 90 days doesn’t shrink the work the same way, because each total still has to start from everything before the window. At real scale I’d materialize it: a daily_balance table with one row per customer per day that had revenue and the running total stored, extended once a day instead of recomputed:”

-- once a day, after the UTC day closes
INSERT INTO daily_balance
  (customer_id, day, revenue_cents, running_revenue_cents)
SELECT d.customer_id, d.day, d.revenue_cents,
       COALESCE(prev.running_revenue_cents, 0) + d.revenue_cents
FROM daily AS d
LEFT JOIN LATERAL (
  SELECT b.running_revenue_cents
  FROM daily_balance AS b
  WHERE b.customer_id = d.customer_id AND b.day < d.day
  ORDER BY b.day DESC
  LIMIT 1
) AS prev ON true
WHERE d.day = (now() AT TIME ZONE 'UTC')::date - 1;

“Here daily is the daily step saved as a view, and a primary key on (customer_id, day) makes finding the previous balance one index lookup. The follow-up is late data: a backdated order, or a refund recorded after its day has closed, changes every balance from its day forward. So the job doesn’t patch one row. It deletes that customer’s rows from the earliest changed day and rebuilds them from the last good balance.”