Date Spine Construction and Zero-Fill Patterns in SQL
A date spine is a complete, gap-free sequence of dates or periods that serves as the structural backbone of a time-series query. Joining a fact table to a date spine with a LEFT JOIN ensures every period in the desired range appears in the result, even periods where no activity occurred in the underlying data.
A date spine is a complete, gap-free sequence of dates that you join your fact data to, so that every period in the desired range appears in the result — even periods with no activity.
Your sales table only has rows for days with sales. Group by day and you get no row for quiet days. When a report or chart requires continuous dates with explicit zeros, the fact table alone can't provide the structure. The spine generates the dates; the LEFT JOIN attaches what fact data exists.
The pattern has three fixed parts: a CTE containing the generated spine, a LEFT JOIN from the spine to the fact table, and COALESCE to convert NULL measures to zero.
WITH spine AS (
SELECT generate_series(
'2024-01-01'::date,
'2024-12-31'::date,
'1 day'::interval
)::date AS day
)
SELECT
s.day,
COALESCE(SUM(o.total_amount), 0) AS daily_revenue,
COALESCE(COUNT(o.id), 0) AS order_count
FROM spine s
LEFT JOIN orders o ON o.ordered_at::date = s.day
GROUP BY s.day
ORDER BY s.dayThe spine drives the result. Every month in the spine appears regardless of whether orders exist. Months with no orders produce NULL in the revenue column after the LEFT JOIN. COALESCE converts those NULLs to zero. The LEFT JOIN to the pre-aggregated monthly CTE guarantees one row per generated month.
The one thing that trips people up
Two things can silently break this pattern:
First, the spine must be the left table. The fact table must be on the right. A regular JOIN or a fact-table-first join drops spine rows with no matches, eliminating exactly the zero-fill rows you're building the pattern to produce.
Second, the join key must match exactly. If your fact table stores timestamps (2024-03-15 14:32:00) and the spine has plain dates (2024-03-15), the join misses rows where the time component is non-zero. Truncate the fact table's timestamp in the ON clause:
LEFT JOIN orders o ON o.created_at::date = s.dayOr use date_trunc('day', o.created_at)::date = s.day for cleaner intent.
Non-daily spines
Change the step interval for weekly, monthly, or hourly spines. For monthly spines, use '1 month'::interval starting from the first of the month — not '30 days' or '31 days'. The '1 month' step lands correctly on the first of each subsequent month regardless of month length.
Once the zero-fill is in place, aggregate window functions work correctly across the full continuous series — running totals and rolling averages no longer skip dates because every date has an explicit row.
Why this pattern matters
Most visualization tools and downstream processes expect complete time series. A chart library that receives data with missing dates will typically connect the dots across the gap, which misrepresents the data. A rolling average applied to a sparse series counts across a calendar window that contains fewer rows than expected. The date spine + LEFT JOIN + COALESCE pattern is what makes both of these work correctly. It's one of the most commonly needed structures in time-series analytics, and once you have it memorized, it takes less than a minute to write.
10 Date Spine Construction and Zero-Fill Patterns practice problems
Write a query to return each date from January 1, 2024 through January 7, 2024 alongside the number of `orders` placed on that date.
Write a query to return each date from March 1, 2024 through March 7, 2024 alongside the number of `events` recorded on that date.
Write a query to return each date from January 1, 2024 through January 31, 2024 alongside the number of `orders` placed on that date.
Write a query to return each date from January 1, 2024 through January 7, 2024 alongside the total order revenue for that date.
Write a query to return each date from March 1, 2024 through March 7, 2024 alongside the number of purchase `events` recorded on that date.
Write a query to return each date from January 1, 2024 through January 7, 2024 alongside the number of `orders` placed on that date and the total number of `orders` placed from January 1, 2024 through that date inclusive.
Write a query to return each date from March 1, 2024 through March 7, 2024 alongside the number of `events` recorded on that date and the total number of `events` from March 1, 2024 through that date inclusive.
Write a query to return each date in the series — starting on March 15, 2024 and ending on March 1, 2024 — alongside the number of `orders` placed on that date.
Write a query to return every date from January 1, 2024 through January 7, 2024 on which no `orders` were placed.
Write a query to return each date from March 1, 2024 through March 7, 2024 alongside the number of `events` recorded on that date and the running average daily event count from March 1, 2024 through that date inclusive.
These problems are part of the Date Spine Construction and Zero-Fill Patterns lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.