Why does a zero-order month disappear from the demand trend?

Prepare the diagnostic input

A trend over August through October requires three periods even if September has no orders. Grouping only order rows cannot invent the absent month. Begin with the approved calendar and left join the complete order extract, counting matched order IDs rather than calendar rows.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE months(period TEXT PRIMARY KEY);
CREATE TABLE orders(id TEXT PRIMARY KEY,placed_on TEXT);
INSERT INTO months VALUES ('2026-08'),('2026-09'),('2026-10');
INSERT INTO orders VALUES ('O1','2026-08-02'),('O2','2026-10-03');

Reproduce the misleading result and correct it

SELECT strftime('%Y-%m',placed_on) AS period,COUNT(*) AS order_count FROM orders GROUP BY period ORDER BY period;

SELECT m.period,COUNT(o.id) AS order_count FROM months m LEFT JOIN orders o
ON strftime('%Y-%m',o.placed_on)=m.period GROUP BY m.period ORDER BY m.period;

The misleading series jumps from August to October, each with one order. The correction inserts September with zero. Zero is justified only because the fixture explicitly contains the complete extract for that calendar; an incomplete export must remain an evidence gap.

Check the boundary before applying the fix

Add a November order. It does not expand the configured August–October report. To include November, change the reporting calendar deliberately.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO orders VALUES ('O3','2026-11-01');

This is order count, not demand forecasting. Cancelled/draft treatment, timezone and period completeness must be defined for an actual system.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.