0:00Most sequel interview questions come from one of four families, and the wording of the prompt tells you which one. This video works the time-series family, calculations across an ordered series, and ends with all four families on one card.
0:14Here's the data. One table of six orders powers every example. Each points back at a customer through customer I-D, with a date, an amount, and a status.
0:24Except the last one. Order one-oh-six has no customer at all, and it's cancelled. It still counts in a running total, which the first query shows.
0:33Small table. But the traps in this video are already sitting in it.
0:37For time-series, the word to listen for is running. Or cumulative. Each order, plus everything before it.
0:45Running total means window sum. Every roe survives.. each just gains a cumulative column. No group by, nothing collapses.
0:54The order by inside the window makes it cumulative. Date.. then order I-D. That second key looks optional. It is not.. and here's the part most candidates miss.
1:08Order by date alone, and watch the two same-day orders. Both say one hundred.
1:13They share a date, so they're peers.. and the default frame is a range frame, which includes "all" peers of the current roe. Tied roes finish together. Both get the total of both.
1:27Add the unique order I-D, and the tie dissolves. Forty.. then one hundred. The tiebreaker changed the "values", not just the display order.
1:37The full accumulation. Forty, one hundred, one twenty-five.. climbing to two hundred eighty.
1:43And notice what's inside that total. Order one-oh-six.. cancelled, no customer, still counted. The prompt never said completed revenue. If it had, you'd add a status filter and say so.
1:57One caveat to close. This display is sorted for reading. The window's order by controls the math.. only an outer order by guarantees the order roes come back in.
2:07Last time-series prompt, and it's the one with a hidden step. Monthly revenue, and its change from the previous month. But look at what we actually have.. orders. Order grain, not month grain.
2:20Lag over raw orders would compare orders. The question compares months. So the real first move is changing the grain.
2:28Six orders collapse into three monthly totals. One twenty-five.. one twenty-five.. thirty. Now the data matches the question.
2:38In sequel, that consolidation is a CTE. Group by year "and" month.. both, so January of different years never merge into one bucket. Postgres and BigQuery folks often write date trunc instead.. same idea, dialect spelling.
2:56Then the comparison. Lag of revenue reads the previous roe's value, in year-month order. Current minus previous.. that's the change. And it compares months only because the CTE already built months.
3:09Three months back.
3:10January's change is NULL. Nothing comes before January.. so there's nothing to subtract. That NULL is expected, and saying so out loud is an interview point in itself.
3:21February, unchanged.. zero. March, down ninety-five.
3:26One edge case before we leave this one. Suppose February had no orders at all.
3:32These two roes are the only February we have. Take them away and the month simply isn't in the data.
3:38Lag doesn't invent a February roe. March's previous available roe becomes January.. so the change is computed across a two-month gap.
3:47If the question needs consecutive calendar months, build a complete month series first, then decide what a missing month's revenue means.
3:55So here are all four families on one card.
3:59Ranking prompts.. latest, top N, Nth highest. Roe number or dense rank, then a filter outside the window. Always name your survivor order and your tie behavior.
4:10Presence and absence. Repeats are group and count, with having. Missing matches are not exists.. never not in against a nullable column. Add date windows, and the same template answers active in A but not B.
4:24Time-series. Running means window sum.. and check your ordering peers. Change versus previous means fix the grain first, then lag.
4:33And conditional aggregates.. CASE inside the aggregate. Count wants non-matches left as NULL. Sum can take explicit zeros.
4:42Name the family.. check its edge case.. then write the template you already know. All four are on this card.. and that's the whole method.

SQL Interview Prompts: Time-Series

Created by Abhyuday DesaiPublished 3 Oct, v1Runs 4 min 58 sec

0 views

About this video

The time-series family of SQL interview prompts, on one small orders table: a running total as a window SUM, where same-day rows share a total under the default RANGE frame until a tiebreaker is added; month-over-month change, where the first move is changing the grain in a CTE before LAG; and what happens when a month has no rows. The video closes on the cheat sheet for all four families: the family, its tell, and its construct.

You'll review how to:

Write a running total with an ordered window SUM, and say why the ORDER BY needs a unique key
Fix the grain in a CTE before comparing periods with LAG, and explain the first period's NULL
Spot a missing period, and when to build a complete series first
Name the family of a prompt from its wording and reach for its template

For engineers who know window functions and CTEs. The cheat sheet is the series' one-card summary.

What changed

v103 Oct 2026abhydesai4 min 58 sec037acd5

First published version.

0 views0 likes on this version