25 Must-know Scenario based Time Series SQL

🚨Time Series SQL is the #1 skill gap I see in Data interviews.

Most candidates can write a basic SELECT statement. But the moment an interviewer says "show me MoM growth" or "calculate Day-30 retention" many go blank.

I've compiled 25 Must-Know Scenario-Based Time Series SQL Interview Questions- the exact patterns asked at top product & service-based companies.

What's covered:

Part 1 - Time Series Fundamentals

✅What makes time series SQL different?

✅Filling missing dates with a calendar spine

✅Longest consecutive order streak (Gaps & Islands)

Part 2 - Running Total & Cumulative Sum

✅Daily running revenue

✅Cumulative sales per product (PARTITION BY reset)

Part 3-LAG() & LEAD()

✅Previous day's revenue side by side

✅Days between a customer's current and next order

Part 4 - Growth Metrics

✅Day-over-Day (DoD) % growth

✅Week-over-Week (WoW) signups

✅Month-over-Month (MoM) revenue

✅Year-over-Year (YoY) - monthly & daily grain

Part 5 - Moving Averages & Rolling Windows

✅3-Day & 7-Day moving averages

✅Rolling 12-Month (TTM) sales

✅Peak sales day detection per month

Part 6 Cohorts, Retention & Churn

✅First purchase date & amount

✅Cohort analysis by first purchase month

✅Cumulative active users over time

✅Day-1, Day-7, Day-30 Retention in one query

✅Monthly Churn Rate calculation

Part 7 Window Functions: Real Interview Scenarios

✅DENSE_RANK() vs RANK() vs ROW_NUMBER()

✅% contribution of each day to its month's total

✅First day cumulative revenue crossed $1M threshold

The golden rule I follow:

The moment you hear "trend, growth, streak, rolling, previous period" think Window Functions, not GROUP BY alone.