SQL Window Functions and Cohorts
Rank, compare and track users over time with window functions and clean CTEs
A taste of a lesson
I need each customer's most recent subscription row. GROUP BY with MAX(date) loses the other columns. What should I do?
This is a classic window function job. In a CTE, add ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY start_date DESC) AS rn. Each customer's rows get numbered from newest to oldest, and every column stays. Then in the outer query, select from the CTE where rn = 1. If two rows share the same date, add a tie breaker such as the subscription ID to the ORDER BY so the result is stable. Check: does your result have exactly as many rows as distinct customers?
Written by the teacher as an example. In your lesson the tutor answers your own questions, and like any AI it can be wrong.
What you will be able to do
- Explain how window functions differ from GROUP BY
- Rank rows and select first or latest records per entity
- Compare periods with LAG and LEAD and build running totals
- Use frame clauses correctly for moving averages
- Build a cohort retention table with readable CTEs
Lesson plan
- 1 Windows versus groups Understand what window functions do that GROUP BY cannot. Start
- 2 Ranking and deduplication Use ranking functions to pick first, latest or top rows. Start
- 3 Previous and next rows Compare periods with LAG and LEAD. Start
- 4 Running totals and frames Build cumulative sums and moving averages with explicit frames. Start
- 5 Readable queries with CTEs Structure complex queries as named steps. Start
- 6 Cohort retention Assemble a retention table by signup month. Start
Try asking
About this tutor
An intermediate tutor for analysts who know basic SQL and want to answer harder questions without exporting to a spreadsheet. You will use PARTITION BY and ORDER BY in window functions to rank rows, find each customer's first and latest event, compare with the previous period using LAG and LEAD, build running totals and moving averages, and assemble cohort retention tables. Lessons also cover writing readable queries with common table expressions, frame clauses, deduplication and checking results. Examples use orders, subscriptions and app events, and the tutor flags dialect differences.
Reviews
4.7
3 ratingsSample
- Precious M.Sample
I can now build retention tables without exporting to spreadsheets. Clear and precise.
- Dalia R.Sample
Finally understand frames. The ties surprise with the default frame explained a weird running total I had.
- Felix O.Sample
Cohort lesson was excellent. CTE step by step style made my queries readable for colleagues.
About the teacher
Data cleaning, SQL, exploratory analysis and honest charts
9 tutors 427 lessons taught Sample
I teach the part of data science that takes most of the time: getting data into a shape you can trust, querying it, exploring it and showing it honestly. I came to data from operations work, where reports drove real decisions and a wrong join could cost a week. I teach by handing you small, deliberately messy tables and asking...
See Lin's profile and tutorsMore like this
Other tutors on the same or nearby topics.