Postgres in depth · Chapter 27 of 65
Streaks and trends
By the end of this chapter you can compute running totals and moving averages, choose exactly which rows a window adds up, compare a row with the one before or after it, and find streaks in a list of dates. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Subqueries, CTEs and windows
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Arjun's dashboard needs trends, not totals: revenue month by month with the running total beside it, and an average that smooths out a bad week. Sana wants to know how long students wait between purchases. Kabir wants the feature every learning app has: streaks, the number of days in a row a student has learned.
Each of these looks at rows near the current one: the months so far, the order before this one, yesterday. Chapter 26's windows ranked rows; this chapter makes the window move.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
monthly-revenue.sql: running total and moving average
Arjun wants LCO's revenue month by month: the sum of total over paid orders, by the month of paid_at (India time). Beside each month, the running total so far and the moving average of this month and the two months before it (fewer at the start).
Return four columns: month (a date, the first day of the month), revenue, running_total and moving_avg (rounded to 2 decimals). Order by month.
Careful: the average must cover exactly the current row and the two rows before it.
- Task 2
order-gaps.sql: days since the previous order
Sana wants to see how often students come back. List every paid order with the number of days since the same student's previous paid order: the number of calendar days between the two paid_at dates (India time). A student's first paid order has none (NULL).
Return four columns: name (the student's), paid_at, total and days_since_previous. Order by name, then paid_at.
Careful: the previous order must be the same student's, never someone else's.
- Task 3
section-ends.sql: next lesson and last lesson
The SQL Fundamentals player shows, under each lesson, the next lesson in the same section and the section's last lesson.
Return five columns: section (the section's position), position (the lesson's), title, next_lesson (the title of the next lesson in the same section, NULL for the last one) and last_in_section (the title of the section's last lesson, on every row). Order by section, then position.
Careful: with only an order in its window, last_value returns the current row's own value. Its frame must reach the end of the section.
- Task 4 · interview question
Interview: streaks.sql, every student's longest streak
LCO is adding learning streaks. A streak is a run of consecutive calendar days (India time) on each of which a student completed at least one lesson (lesson_progress with completed_at). For every student who completed a lesson, find their longest streak and the day it started; when two streaks are equally long, take the earlier one.
Return three columns: name, longest_streak (days) and started_on (a date). Any order.
Careful: several lessons on one day count as one day. A day with nothing completed breaks the streak.
What you will learn
- Running totals: sum(...) OVER (ORDER BY ...).
- Frames: ROWS BETWEEN ... AND ..., and how ROWS differs from the default RANGE.
- lag and lead: the value from the previous or next row.
- first_value and last_value, and the last_value trap.
- Gaps and islands: grouping consecutive days into runs.
More in Subqueries, CTEs and windows
Build "Streaks and trends" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.