Lesson 12 of 14 · 28 min
Time series without gaps: date spines
By the end of this lesson you can generate a date spine with generate_series, LEFT JOIN facts onto it so missing periods appear as zeros, cross join a spine with categories, and avoid the COUNT(*) and WHERE traps that make empty periods disappear.
Group orders by day and you get one row for every day that had orders. A day with no orders isn't a row with zero; it's simply absent. That's harmless in a table read by a person and dangerous everywhere else:
- a line chart draws a straight line across the missing days, hiding the gap;
AVG(orders)over the rows becomes the average of active days, not of all days;ROWS 6 PRECEDINGreaches back to rows that may be weeks old.
The fix is a date spine: a table with exactly one row per period, generated by generate_series, to which the data is LEFT JOINed.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons