Date Spine Construction and Zero-Fill Patterns in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this generate_series() for Sequences and Date Spines, LEFT JOIN and RIGHT JOIN, Aggregate Window Functions (SUM, AVG, COUNT OVER)
Builds toward Sessionization and Funnel Analysis Patterns
What are Date Spines in SQL?
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.
How do you fill missing dates with zeros in SQL?
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.dayWITH spine AS (
SELECT generate_series('2024-01-01'::date, '2024-12-01'::date, interval '1 month')::date AS month
),
monthly AS (
SELECT date_trunc('month', ordered_at)::date AS month, SUM(total_amount) AS revenue
FROM orders GROUP BY 1
)
SELECT s.month, COALESCE(m.revenue, 0) AS revenue
FROM spine s
LEFT JOIN monthly m ON m.month = s.month
ORDER BY s.monthThe 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.
Why does a date spine still drop empty periods?
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.ordered_at::date = s.dayOr use date_trunc('day', o.ordered_at)::date = s.day for cleaner intent.
How do you build a weekly or monthly date spine?
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 does a chart need a gap-free date series?
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.
Practice Date Spines in SQL
Scenario: Brightlane's fulfillment operations team is sizing daily staffing against last year's order volume and needs a complete view of the first week of January 2024.
Task: Write a query to return each date from January 1, 2024 through January 7, 2024 alongside the number of orders placed on that date.
Assumptions:
- The
orderstable holds one row per placed order, with the placement timestamp stored inordered_at. - Some dates in the range have no recorded
orders; those dates must still appear in the result with a count of zero.
Output:
- One row per date in the range, including dates with no
orders. - Columns in this order:
day,order_count. - Sorted by
dayascending.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Date Spines 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.
Start learning to practice all 10 Date Spines problems, with instant grading and mastery tracking.
Common questions about Date Spines
How do I show zero for days with no data in SQL?
Build the complete list of dates first, then LEFT JOIN your data onto it and wrap the aggregate in COALESCE(..., 0). The LEFT JOIN keeps every date in the list even when nothing matches, and COALESCE turns the resulting NULL into a zero.
Why does a day with no data disappear instead of showing zero?
GROUP BY can only group rows that exist. A day with no orders has no row to group, so it never reaches the result. Nothing errors and nothing warns you: the day is simply absent, which is why gaps in a daily report are easy to miss.
Does the date spine go on the left or the right of the join?
The left. A LEFT JOIN keeps every row from its left-hand table, so the spine has to be there for every date to survive. Put the spine on the right and you are keeping every order instead, and the empty days vanish again.
Why count a column instead of using COUNT(*) on a date spine?
COUNT(*) counts rows, and the LEFT JOIN still produces one row for an empty day, so COUNT(*) reports 1 for a day that had nothing. COUNT(orders.id) counts only non-NULL values, and an empty day has a NULL id, so it correctly reports 0.
