Articles
SQL Date Spine Interview Questions: Fill Missing Dates Without Inflating Metrics
Build SQL date spines without inflating metrics: define eligible keys, aggregate facts first, preserve empty days, and validate totals.
Your daily sales report skips Tuesday because no orders arrived. You add a date spine and the chart becomes continuous—but Monday's revenue doubles after a refunds join. Filling dates solved the missing-row problem while exposing a second problem: the query no longer respects the metric's grain.
A date spine is a set of expected date keys. It does not deduplicate facts, establish data completeness, or decide whether absence means zero. The reliable pattern is define eligible output keys, aggregate each fact source to those keys, and only then join.
This guide uses an original two-shop exercise with deliberately multiplying joins. Official PostgreSQL documentation supports SQL semantics; the examples and design recommendations are editorial reasoning, not reported employer questions. For related practice, try Compute DAU and rolling MAU with zero days.
Decide which rows should exist before examining facts
For a business-wide daily report, a date spine contains one row per reporting day. For a shop-level report, the required output grain is shop–day. A calendar alone cannot specify which shops should appear on each day.
Assume two shops. Shop A opens on January 1; shop B opens on January 2. The report covers January 1 through January 3, 2026. Both shops remain open. Their data is complete for all eligible dates, and timestamps already use the reporting timezone. A day with no orders therefore means zero orders, not unknown ingestion.
The expected key set contains five rows: A on all three days, and B on the last two. B–January 1 should not appear as a zero-sales day because B was not yet eligible. If your business instead requires prelaunch rows, that is a different declared population rule.
Use a dimension or authoritative lifecycle table to define the population. Deriving shops only from orders drops shops with no activity at all. Deriving report bounds only from minimum and maximum transaction dates can similarly omit the quiet days at either end of the requested range.
A fixture that makes fan-out visible
The source tables are shops(shop_id, opened_on, closed_on), orders(order_id, shop_id, order_date, amount), and refunds(refund_id, shop_id, refund_date, amount). IDs are unique. A closure date, when present, is the first ineligible day. Amounts are non-NULL decimal values in one currency.
Orders contain A–January 1 amounts 10 and 20, plus B–January 2 amount 7. Refunds contain A–January 1 amounts 2 and 3. Refunds are attributed to their processing date for this exercise; attributing them back to the original order date would require a different definition.
| Shop–day | Order count | Gross sales | Refund amount | Net amount |
|---|---|---|---|---|
| A–Jan 1 | 2 | 30 | 5 | 25 |
| A–Jan 2 | 0 | 0 | 0 | 0 |
| A–Jan 3 | 0 | 0 | 0 | 0 |
| B–Jan 2 | 1 | 7 | 0 | 7 |
| B–Jan 3 | 0 | 0 | 0 | 0 |
The report totals are gross sales 37 and refunds 5. Those totals must survive the addition of zero-activity rows.
If you join both raw fact tables to A–January 1, two orders match two refunds, producing four combinations. Summing the joined order amounts gives 60, and summing refunds gives 10. A calendar has not created new business activity; the join has repeated it.
Aggregate each source before joining the spine
This complete query implements the five-row contract:
WITH dates AS (
SELECT CAST(day AS DATE) AS day
FROM generate_series(
TIMESTAMP '2026-01-01 00:00:00',
TIMESTAMP '2026-01-03 00:00:00',
INTERVAL '1 day'
) AS g(day)
), spine AS (
SELECT s.shop_id, d.day
FROM shops AS s
CROSS JOIN dates AS d
WHERE d.day >= s.opened_on
AND (s.closed_on IS NULL OR d.day < s.closed_on)
), daily_orders AS (
SELECT shop_id, order_date AS day,
COUNT(*) AS order_count,
SUM(amount) AS gross_sales
FROM orders
WHERE order_date >= DATE '2026-01-01'
AND order_date < DATE '2026-01-04'
GROUP BY shop_id, order_date
), daily_refunds AS (
SELECT shop_id, refund_date AS day,
SUM(amount) AS refund_amount
FROM refunds
WHERE refund_date >= DATE '2026-01-01'
AND refund_date < DATE '2026-01-04'
GROUP BY shop_id, refund_date
)
SELECT s.shop_id, s.day,
COALESCE(o.order_count, 0) AS order_count,
COALESCE(o.gross_sales, 0) AS gross_sales,
COALESCE(r.refund_amount, 0) AS refund_amount,
COALESCE(o.gross_sales, 0)
- COALESCE(r.refund_amount, 0) AS net_amount
FROM spine AS s
LEFT JOIN daily_orders AS o
ON o.shop_id = s.shop_id AND o.day = s.day
LEFT JOIN daily_refunds AS r
ON r.shop_id = s.shop_id AND r.day = s.day
ORDER BY s.shop_id, s.day;
Official semantics: PostgreSQL's generate_series creates a sequence from the specified start to stop using the step. Here, the stop is the last desired daily key. See set-returning functions.
Both aggregate relations have at most one row per shop–day. Joining them to a unique spine therefore does not multiply that key. This is the correctness argument to explain before discussing execution plans.
The query was executed in DuckDB 1.5.2 against the fixture and returned the five rows above. It uses PostgreSQL-style series syntax supported in that runtime; no PostgreSQL benchmark is claimed.
Keep filters from undoing the outer join
A tempting rewrite places orders.status = 'completed' in a final WHERE clause after a left join to raw orders. On a day with no orders, that column is NULL, so the condition does not retain the generated date. The query has accidentally removed the empty days it intended to preserve.
Filter qualifying facts inside the source CTE, as the query does with dates, or use the appropriate join predicate while preserving the intended outer behavior. Official PostgreSQL documentation explains the difference between conditions in an outer join's ON clause and conditions applied later in WHERE. See table expressions.
Counting also needs care. A raw left join retains a placeholder row for an unmatched day. COUNT(*) counts that row; COUNT(order_id) does not count a NULL order ID. In our query, COUNT(*) is correct inside daily_orders because that CTE contains real order rows, before the outer join introduces placeholders.
Official aggregate semantics: PostgreSQL distinguishes counting all rows from counting non-NULL expression values. See aggregate functions. The location of the aggregation determines which population is being counted.
DISTINCT is not a general repair for inflated sums
After seeing duplicated amounts, someone may propose SUM(DISTINCT amount). That fails when two legitimate orders have the same value. Two separate orders worth 10 each should contribute 20; a distinct amount sum contributes 10.
Likewise, SELECT DISTINCT on the final report can remove identical output rows without undoing inflation that already happened inside an aggregate. Deduplicating business events and deduplicating numerical values are different operations.
If the source itself contains retried events, establish a real deduplication key and precedence rule before daily aggregation. An order ID, event ID, or source version may be relevant; an amount alone is not enough. If an order can have several legitimate adjustments, blindly keeping one row per order could discard meaningful activity.
The spine should join to a trustworthy daily measure. It cannot determine whether two source records represent a retry, a revision, or two actual purchases.
Zero-fill only when absence has that meaning
Our fixture assumes complete inputs and additive measures. A missing daily aggregate can therefore become zero sales or zero refunds. The same replacement is not automatically valid for temperature readings, inventory snapshots, exchange rates, or average delivery time.
A day without a sensor observation does not establish a reading of zero. A missing balance snapshot does not necessarily mean the account was empty. Carrying a value forward may be appropriate for some state measures, but that is an explicit temporal assumption, not a date-spine feature.
For incomplete ingestion, retain completeness metadata alongside the metric. A generated row could have data_status = 'incomplete' and a NULL amount. Distinguish this from an eligible complete day with genuine zero activity and from a day outside the entity's lifecycle.
That separation matters for downstream averages. Averaging known zero days changes the denominator legitimately when the metric is “per eligible calendar day.” Treating unknown days as zeros creates a different and potentially biased estimate.
A dense calendar changes window meaning
On one row per consecutive shop–day, a seven-row trailing window can represent seven calendar days. On sparse facts, seven rows may span much longer. Official PostgreSQL window behavior distinguishes row-based frames and their ordering; it does not automatically supply absent dates. See window functions.
Even after densifying, define the beginning of the series. The first displayed day may need six earlier days loaded to calculate a complete trailing seven-day metric. Alternatively, label early windows as partial. Filtering the history too soon changes the calculation.
Entity eligibility adds another choice. A shop that opened three days ago has only three eligible days. Do you want an average over those three days, or a fixed seven-day denominator that includes four prelaunch zeros? Neither answer should emerge accidentally from how the cross join was written.
Also distinguish a rolling sum of daily distinct users from distinct users across the whole window. A person active on three days contributes three to the sum of daily distinct counts but one to the window's distinct-user count. Adding a spine does not make those measures interchangeable.
Rates need matching numerator and denominator policies
Suppose the report includes daily conversion rate. Aggregate eligible conversions and eligible opportunities under compatible definitions, then divide. A no-opportunity day has a zero denominator; its rate is not automatically zero simply because a date row exists.
When summarizing several days, the average of daily rates and total conversions divided by total opportunities answer different questions. A day with one opportunity and a day with a thousand receive equal weight in the first approach. The second weights by opportunity count.
For the shop fixture, a refund-to-sales ratio can also be misleading if refunds are processed on dates different from their original purchases. Our “net amount” is a daily flow measure under a declared processing-date policy, not a cohort refund rate. Naming the measure clearly avoids implying a relationship the query does not establish.
Control the size of the scaffold
A global cross join of every entity and every date can be enormous. Restrict the date range and entity population to the requested analysis, then apply lifecycle eligibility. Estimate expected key count before combining the scaffold with facts.
A persistent calendar dimension can hold business days, fiscal periods, holidays, and local reporting attributes. A generated series is convenient for a bounded exercise. The choice should follow reuse and business semantics, not a belief that one technique is universally faster.
For multiple active periods, an entity may have several lifecycle intervals. Ensure those intervals do not overlap for the same entity, or the scaffold itself can produce duplicate entity–day keys. The fact aggregates can be perfectly correct while duplicated dimension intervals still multiply the final output.
Convert event timestamps to reporting dates consistently before aggregation. Daily boundaries in a local timezone can differ from UTC boundaries, especially near midnight and daylight-saving changes. A date spine represents calendar labels; event assignment still needs a time policy.
Validate keys and totals together
The local test checks five unique output keys, gross total 37, refund total 5, and no B–January 1 row. It also runs the deliberately bad raw join and confirms the inflated A–January 1 values of 60 and 10. That counterexample makes the repair observable.
A second mutation gives both A orders the same amount. The correct query still sums both orders, while a distinct-amount shortcut would fail. This checks the business identity of rows rather than relying on conveniently unique fixture values.
In production, reconcile totals over the same eligible population and period. Orders assigned to closed shops or outside the reporting range may be intentionally excluded, but they should be counted and explained separately. An unexplained difference is not resolved merely because the final chart has no gaps.
Practice calendar and grain reasoning
| Question | What to explain |
|---|---|
| Compute DAU and rolling MAU with zero days | Calendar completeness without confusing daily and rolling distinct counts |
| Compute a Seven-Day Rolling Average | Calendar distance versus row count |
| Write SQL to compute shop visibility share | Eligible shop–day denominators and deduplication |
| Compute daily post success rate for last 7 days | Preserve dates while treating empty denominators honestly |
| Debug row loss after SQL joins | Keep source filters from deleting generated rows |
Start with Compute DAU and rolling MAU with zero days, then describe the expected output keys before writing any joins.