MODULE 3 OF 8: AGGREGATE QUERIES • LESSON 4 OF 4~20 min read

Date & Time Analytics: Cohort Retention, Growth and Moving Windows

Master business date calculations: cohort retention matrices, Month-over-Month (MoM) revenue growth, date truncation, and rolling intervals.

•

Previous Next Lesson

Master business date calculations: cohort retention matrices, Month-over-Month (MoM) revenue growth, date truncation, and rolling intervals.

Key Takeaways at a Glance:

•DATE_TRUNC('month', date) truncates timestamps to the beginning of the period, enabling clean grouped cohort aggregation.
•Month-over-Month (MoM) growth compares current month metrics to the previous month using LAG(revenue, 1) OVER (ORDER BY month).
•Rolling 7-day windows use 'ROWS BETWEEN 6 PRECEDING AND CURRENT ROW' to smooth out day-of-week fluctuations.
•Day-N cohort retention measures what percentage of users who signed up on Day 0 return on Day N.

Foundational Principles

Core Concept Breakdown

Essential database invariants, relational theory, and execution behavior.

1The Mental Model of Time in SQL

Time-series querying is foundational to software engineering and data analytics. Relational databases store temporal data using DATE (day precision), TIME (clock time), and DATETIME / TIMESTAMP (microsecond precision with timezone awareness). In analytics, always use half-open intervals: [start_date, end_date) where WHERE event_time >= '2026-03-01' AND event_time < '2026-04-01' to avoid missing records that occur on the final day's final microsecond.

Free Preview Limit Reached (2/2 Lessons Used)

Sign In to Unlock Full Lesson Content

Visitors are limited to 2 free preview lessons. Create a free StudyVex account to unlock all 38 comprehensive theory chapters, runnable query walkthroughs, visual input & result tables, and interactive placement quizzes.