SQL problem track

Windows & Time Series

Running totals, trailing averages, growth, and share of total — every question here turns on the frame.

4 problems · ~52 min total · Data Engineer · Analytics Engineer · Data Scientist · Data Analyst

1. Step 1 · ~12 min · Stripe, Airbnb, Booking

   Running total of daily revenue

   Finance wants a cumulative revenue column beside the daily number so they can see the month build up. Aggregate to a day first, then run the window over that result — not over raw orders.

   Window functionsTime series

   mediumProNot started

2. Step 2 · ~14 min · Netflix, Spotify, Uber

   Seven-day moving average

   Daily revenue is noisy, so the dashboard smooths it over a trailing week. The frame has to include the current day and the six before it — off-by-one here is the most common review comment on this query.

   Window functionsTime series

   hardProNot started

3. Step 3 · ~14 min · Meta, Uber, Lyft

   Day-over-day revenue growth

   Put yesterday’s revenue and the percent change beside each day. The first day has no prior row, and the answer must leave it NULL rather than inventing a zero.

   Window functionsTime series

   hardProNot started

4. Step 4 · ~12 min · Amazon, Shopify, Meta

   Revenue share by country

   Each country needs its percentage of total completed revenue on the same row as its own revenue. A second query for the grand total works but scans the table twice — do it with one window instead.

   Window functionsAggregation

   mediumProNot started

Other tracks