Track running order revenue by channel
Your task
Return channel, order_id, and running_cents for completed shop orders. Start a separate total per channel and add orders by date then ID. Sort by channel, date, then ID.
A running total restarts for each partition and advances one row at a time.
Sample data
shop_orders| id | customer_id | ordered_on | channel | status | total_cents |
|---|---|---|---|---|---|
| 201 | 1 | 2025-01-04 | online | completed | 2500 |
| 202 | 2 | 2025-01-10 | store | completed | 1800 |
| 203 | 2 | 2025-01-10 | store | completed | 3200 |
| 204 | 3 | 2025-01-18 | marketplace | refunded | 5000 |
| 205 | 4 | 2025-02-02 | store | completed | 2700 |
| 206 | 1 | 2025-02-04 | online | completed | 4000 |
| 207 | 5 | 2025-02-18 | online | cancelled | 2400 |
| 208 | 6 | 2025-02-28 | online | completed | 1700 |
| 209 | 4 | 2025-03-16 | online | completed | 2900 |
| 210 | 8 | 2025-03-09 | store | completed | 3100 |
Expected output
| channel | order_id | running_cents |
|---|---|---|
| online | 201 | 2500 |
| online | 206 | 6500 |
| online | 208 | 8200 |
| online | 209 | 11100 |
| store | 202 | 1800 |
| store | 203 | 5000 |
| store | 205 | 7700 |
| store | 210 | 10800 |
Show a hint
Use SUM(total_cents) OVER with PARTITION BY channel and a ROWS frame ending at the current row.
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;Worked solution
SELECT channel,id AS order_id,SUM(total_cents) OVER (PARTITION BY channel ORDER BY ordered_on,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_cents FROM shop_orders WHERE status='completed' ORDER BY channel,ordered_on,id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
