Keep zero days and propagate filters consistently

A fact-only daily query omits dates with no orders. Connecting the remaining points can make a quiet day disappear and make activity seem more continuous than it was.

The fixture supplies an explicit calendar of covered business dates. A covered date without matching orders has zero activity. A date outside that calendar has unknown source coverage and must not be treated as a measured zero.

Filter facts by segment and channel before grouping them by date. Then LEFT JOIN the grouped totals from the selected calendar window. Coalesce absent activity to zero.

Avoid a WHERE condition on the nullable joined activity row that removes quiet dates. Keep coverage selection on the calendar and metric eligibility in the facts. The same selected dimensions must drive headline counts, averages, channel bars and daily values.

WITH calendar(day) AS (VALUES('2026-01-01'),('2026-01-02'),('2026-01-03')),
daily(day,orders,net) AS (VALUES('2026-01-01',2,500),('2026-01-03',1,-100))
SELECT c.day,COALESCE(d.orders,0) AS orders,COALESCE(d.net,0) AS net
FROM calendar AS c LEFT JOIN daily AS d USING(day) ORDER BY c.day;

Dates here are already normalized business dates, not event timestamps interpreted in the viewer’s local zone. Production ingestion must define business timezone and refund attribution explicitly; this exercise does not hide those decisions behind a date picker.

The report uses an inclusive start/end range. January 1 through January 7 contains seven dates. A same-length preceding window is December 25 through December 31. The next lesson aligns each current date with its preceding-window counterpart.

Known zero days also matter for reconciliation. Daily cents must sum to the KPI numerator; daily order counts must sum to its denominator. Count zero does not imply a zero average: that day’s per-order average remains undefined.

WITH facts(day,segment,net) AS (VALUES('2026-01-01','Consumer',100),('2026-01-01','Business',900)),
f(segment) AS (VALUES('Consumer'))
SELECT day,COUNT(*) AS orders,SUM(net) AS net
FROM facts CROSS JOIN f WHERE f.segment IS NULL OR facts.segment=f.segment GROUP BY day;

Build the covered series and test an empty dimension selection. It should return the selected covered dates with zeros, not no dates. Check negative activity and inclusive endpoints.

Next we combine these rules in the complete interactive report and define comparison coverage.