A line for every day

O(n²) · Very hard
Full plan
SQL
recursion
dates
window functions

Problem

The notebook by the register jumps from January 4 to the 9th as if nothing had happened: a day with no orders leaves no row in orders, and what has no row doesn't show up. The owner wants the sheet for the days already closed, from January 3 to the 14th, which was yesterday: twelve lines, including the days when nothing came in.

For each day, show three columns, in this order: the date, the money ordered that day (each product's price times the units ordered), and the running total of money ordered since the 3rd, as of the end of that day. Round both amounts to 2 decimals and put the most recent day on top. A day with no orders gets a 0 and doesn't move the running total: the line for January 5 says 0 and 379.5.

Today, January 15, hasn't closed yet: neither today's orders nor the one dated the 20th has a line on this sheet.

What it has to return

These rows, in this order

day	day_total	running_total
2026-01-14	899	1790.15
2026-01-13	0	891.15
2026-01-12	35.65	891.15
2026-01-11	378	855.5
2026-01-10	45.5	477.5
2026-01-09	52.5	432
2026-01-08	0	379.5
2026-01-07	0	379.5
2026-01-06	0	379.5
2026-01-05	0	379.5
2026-01-04	288.5	379.5
2026-01-03	91	91

This is what comes out with the data in the practice database this challenge uses. The column names don’t count, so name yours however you like: what counts is how many there are and the order they come in. Besides this one, the challenge has hidden tests with other data that are revealed when you submit your solution.

You start with this

SQL

SELECT o.placed,
  p.price * o.quantity AS money
FROM orders o
JOIN products p
  ON o.product_id = p.id

Solve this challenge

It opens in your browser, with the editor and the tests. This challenge is part of the full plan; the O(1) and O(log n) ones are free.

More O(n²) challenges

See all challenges →