SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 131 of 250

Hard

Compare running totals by order and by day

Your task

Return id, ordered_on, order_running_cents, and day_running_cents for completed orders. The order total stops at the current order in date and ID order. The day total includes all completed orders through the current date. Sort by date then ID.

A RANGE frame can include peers with the same sort value, while a ROWS frame advances with each individual row.

Sample data

Table:shop_orders
idcustomer_idordered_onchannelstatustotal_cents
20112025-01-04onlinecompleted2500
20222025-01-10storecompleted1800
20322025-01-10storecompleted3200
20432025-01-18marketplacerefunded5000
20542025-02-02storecompleted2700
20612025-02-04onlinecompleted4000
20752025-02-18onlinecancelled2400
20862025-02-28onlinecompleted1700
20942025-03-16onlinecompleted2900
21082025-03-09storecompleted3100

Expected output

idordered_onorder_running_centsday_running_cents
2012025-01-0425002500
2022025-01-1043007500
2032025-01-1075007500
2052025-02-021020010200
2062025-02-041420014200
2082025-02-281590015900
2102025-03-091900019000
2092025-03-162190021900
Show a hint

Use ROWS ordered by date and ID for the first sum, and RANGE ordered only by date for the second.

Window functions guide

Keep detail rows while summarizing

GROUP BY would return one row per genre. A window aggregate with PARTITION BY leaves every album in the result and gives each row its genre's average. This makes it easy to compare a row with its peers.

SELECT title, genre, price,
       AVG(price) OVER (PARTITION BY genre) AS genre_average
FROM albums;

Specify the window order and frame

The ORDER BY inside OVER defines the order of calculation. ROWS defines which neighboring rows contribute. This example adds the current sale to all earlier sales by ID. The last ORDER BY controls the order shown on screen.

SELECT id, quantity,
       SUM(quantity) OVER (
         ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_quantity
FROM sales
ORDER BY id;

Rank first, then filter

Window results are calculated too late for the same SELECT's WHERE clause. Compute a rank in a CTE, then filter the named result. ROW_NUMBER assigns one position per row. RANK gives ties the same position and may leave gaps.

WITH ranked AS (
  SELECT title, genre, ROW_NUMBER() OVER (
    PARTITION BY genre ORDER BY price, id
  ) AS position
  FROM albums
)
SELECT genre, title FROM ranked WHERE position = 1;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…