learn deep. ship safe.Practice testsFollow ByteMunSubscribe

mediumWindow functionsGROUP BY and aggregatesSQL

Daily Sales Running Total

Total the sales for each day, then add a running (cumulative) total across days with SUM() OVER. A practical window-function question for SQL interviews.

SQL

The table sales(id, sale_date, amount) has one row per sale; a day can have several sales. Dates are stored as YYYY-MM-DD.

Write a query that returns one row per day that has sales, with three columns in this order: sale_date, daily_total (the sum of that day's amounts) and running_total (the sum of all amounts up to and including that day). Order the rows by sale_date, oldest first.

Examples

Example 1

Input: sales: (1, 2026-03-01, 100), (2, 2026-03-01, 50), (3, 2026-03-02, 30), (4, 2026-03-04, 20)
Output: sale_date | daily_total | running_total
2026-03-01 | 150 | 150
2026-03-02 | 30 | 180
2026-03-04 | 20 | 200
Why: Days without sales (2026-03-03) don't appear; the running total carries on from the previous day that had sales.

Constraints

  • Order by sale_date ascending.
  • Only days that have at least one sale appear.
  • amount is a positive whole number.

Hints

Hint 1

First collapse the sales to one row per day with GROUP BY sale_date.

Hint 2

A window function can run over the grouped rows: SUM(...) OVER (ORDER BY sale_date).

Hint 3

You can put an aggregate inside a window function: SUM(SUM(amount)) OVER (...).

Solution

Show the solution (try it yourself first)

Approach: group by day, then a window sum

Group the sales by day to get each day's total. Window functions are evaluated after GROUP BY, so a SUM window ordered by date can run over the grouped rows and add up everything so far. A CTE keeps the two steps easy to read.

WITH daily AS (
  SELECT sale_date, SUM(amount) AS daily_total
  FROM sales
  GROUP BY sale_date
)
SELECT sale_date, daily_total,
       SUM(daily_total) OVER (ORDER BY sale_date) AS running_total
FROM daily
ORDER BY sale_date;

The same thing in one step: SUM(SUM(amount)) OVER (ORDER BY sale_date). With ORDER BY inside OVER, the default frame runs from the first row to the current one, which is exactly a running total. Before MySQL 8 people used a correlated subquery per day, which is O(n²).

Complexity

O(n log n): one grouping sort and one ordered pass for the window.

Found a mistake in this problem, its tests or the solution?

Written by Published Tests checked by Deepak
SQL · standard syntax, runs on SQLite
Case 1
Table sales
idsale_dateamount
12026-03-01100
22026-03-0150
32026-03-0230
42026-03-0420
Expected (3 rows)
2026-03-01150150
2026-03-0230180
2026-03-0420200
Case 2
Table sales
idsale_dateamount
12026-05-1075
Expected (1 row)
2026-05-107575

Run checks these 2 samples. Submit checks all 5 tests, including hidden ones.

  1. Highest Paid Employee in Each DepartmentJoins · GROUP BY and aggregates · Window functions · SQL
    medium
  2. Find Duplicate EmailsGROUP BY and aggregates · SQL
    easy
  3. hard
  4. Top Three Salaries per DepartmentWindow functions · SQL
    hard