5 SQL Date Patterns for Interviews
Month-over-month growth, rolling averages, cohorts, weekday splits, days-between events: the 5 SQL date patterns analyst JDs repeat, with queries.
- Month-over-month growth —
DATE_TRUNC('month', d)+LAG(SUM(rev)) OVER (...). The single most-asked pattern. - Rolling 7/30-day averages —
AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). - Cohort retention — signup-month × activity-month matrix, DATE_TRUNC on both sides.
- Weekday vs weekend —
EXTRACT(DOW FROM d)(0 = Sunday; confirm your dialect). - Days between events —
ship_date - order_datewithWHERE ship_date IS NOT NULL. NULL handling decides it.
Learn these five cold and you cover nearly every take-home and live SQL round. Drill them on messy data at Topfolio Practice.
Related: Sql Window Function Traps · Sql 30 Interview Questions Practice
Frequently Asked Questions
What is the most-asked SQL date pattern?
Month-over-month growth with DATE_TRUNC plus LAG. Learn it cold before anything else.
Why do rolling averages trip people up?
They need a window frame (ROWS BETWEEN), which GROUP BY alone can't express.
DATE_TRUNC or EXTRACT first?
DATE_TRUNC for bucketing months and cohorts; EXTRACT for weekday splits and parts. Both appear constantly.
Written by
Founder at Topfolio with 6+ years in data & analytics across JPMC, Ultrahuman, and high-growth startups. Sat on hiring panels, reviewed 500+ resumes, and writes practical SQL & data guides.
Related Articles
5 Window Traps That Lie
Basket-split LAG gaps, ghost orders, nondeterministic ROW_NUMBER, RANK gaps, unframed running totals: 5 window traps with fixes and SQL included.
30 SQL Interview Questions, Free
The 30 SQL patterns analyst interviews repeat — JOINs, windows, GROUP BY traps, NULLs, dates, CTEs — indexed by pattern with live practice links.
4 GROUP BY Traps, Wrong vs Right
WHERE vs HAVING, nullable grouping keys, bare SELECT columns: 4 GROUP BY traps that run without errors yet fail take-homes, each with the fix.