Calendar Zero-Fill — Solution & ExplanationProblems / Calendar Zero-Fill / EditorialApproach

Drive from the calendar. A LEFT JOIN from calendar to orders on o.order_date = c.day keeps every one of the 30 days, whether or not any orders matched.

Count a nullable column. After the join, a day with no orders still produces one row — but every orders column in it is NULL. COUNT(o.order_id) skips NULLs, so those days count as 0. COUNT(*) would count the row itself and report 1.

Group by c.day and order by it.

Key InsightZero-filling is a *join direction* problem, not an aggregation problem. GROUP BY can only produce groups for rows that exist, so missing days can never appear from orders alone — the spine table must supply them. The companion trap is COUNT(*) vs COUNT(column): after a LEFT JOIN, COUNT(*) counts the unmatched row too, while COUNT(o.order_id) counts only real orders. In PySpark the same pair applies: F.count(orders["order_id"]) skips NULLs exactly like SQL.Hints

If you aggregate the orders table alone, days with zero orders simply never appear — you need a driving table that already contains every day.

LEFT JOIN from calendar to orders keeps all 30 calendar days. Days with no match get NULL in every orders column.

COUNT(*) counts rows — including the single all-NULL row an empty day produces after the join, so it would report 1. COUNT(o.order_id) only counts non-NULL values, giving 0 for empty days.

Solve Calendar Zero-Fill yourself →