Write a query to calculate the running total of daily revenue for a store using a self-join.

Answer

A running total via self-join sums every day's revenue for all dates up to and including the current one.

Solution

SELECT r1.sale_date, SUM(r2.revenue) AS running_total
FROM DailyRevenue r1
JOIN DailyRevenue r2 ON r2.sale_date <= r1.sale_date
GROUP BY r1.sale_date
ORDER BY r1.sale_date;

Step by Step

1. The self-join pairs every date (r1) with every date on or before it (r2.sale_date <= r1.sale_date).
2. For a given r1.sale_date, this produces one matching r2 row for every prior day plus itself.
3. SUM(r2.revenue) adds up all of those matched rows' revenue - exactly the cumulative total through that date.

Alternative Method: Window Function (Preferred in Practice)

SELECT sale_date, SUM(revenue) OVER (ORDER BY sale_date) AS running_total
FROM DailyRevenue
ORDER BY sale_date;

The SUM() OVER (ORDER BY ...) window function computes the exact same running total in a single pass over the data, without the quadratic cost of a self-join - this is the version most interviewers actually want to see once you've shown you understand the self-join logic.

Key Exam Traps

• Self-join performance: A self-join running total is O(n²) - fine to demonstrate understanding, but explicitly mention the window function as the production-appropriate choice for large tables.
• Missing dates: If a day has zero sales and no row exists for it at all, the running total silently skips that date rather than showing a flat line - worth flagging if the business needs every calendar day represented.
• Ties in ORDER BY: If multiple rows share the exact same sale_date (e.g. revenue logged per store), the window function's running total needs a tiebreaker or it may double-count within a single date's group depending on the frame - always confirm what grain a "day" actually represents.