Typorithm · medium
Step 1 of 4 · intro card
Retain requested periods while separating complete-log zero from unknown collection coverage.
A report can require calendar periods that have no matching event rows. This teaching scenario explicitly assumes complete fictional added-event capture for the three listed days. Under that contract, no event row on a requested day means zero added events, not unknown monitoring coverage.
The worked query supplies the three requested dates as a small calendar input, aggregates A's events and left joins those totals to the calendar. It returns five, zero and one. A real collector failure would need a separate coverage indicator and could not honestly be filled as zero.
VALUES provides the tiny date scaffold without adding a permanent table.
VALUES provides the tiny date scaffold without adding a permanent table. The daily intermediate result has one row per observed date, preventing multiple first-day events from duplicating a calendar row. LEFT JOIN retains dates with no matching daily aggregate.
COALESCE applies the stated complete-log empty-count policy to an absent daily measure. The fixture uses normalized date-only ISO text; a larger real reporting system should derive a validated calendar in its declared temporal type and timezone rather than guessing date strings.
sql
WITH days(day) AS (VALUES ('2026-10-01'),('2026-10-02'),('2026-10-03')),
daily AS (SELECT day,SUM(added) AS n FROM observations WHERE company_id='A' GROUP BY day)
SELECT d.day,COALESCE(x.n,0) FROM days d LEFT JOIN daily x ON x.day=d.day ORDER BY d.day;The counterexample uses an inner join and removes A's second day.
The counterexample uses an inner join and removes A's second day. A chart based on that result can visually compress time or make a three-day average use only two observed rows. Missing output periods are therefore a measurement issue, not just a presentation inconvenience.
Do not always fill missing periods with zero. For an incomplete scrape, delayed import or outage, absence may mean unknown. Make the distinction explicit in the data contract and test coverage state independently before calculating trends or percentage changes.
sql
WITH days(day) AS (VALUES ('2026-10-01'),('2026-10-02'),('2026-10-03')),
daily AS (SELECT day,SUM(added) AS n FROM observations WHERE company_id='A' GROUP BY day)
SELECT d.day,x.n FROM days d JOIN daily x ON x.day=d.day ORDER BY d.day;Produce the same complete three-day report for company B.
Produce the same complete three-day report for company B. Predict four, two and zero. Change the company population in the daily aggregate while retaining all requested calendar dates, including the third day with no matching B events.
Keep the zero policy tied to this complete fictional log. Do not infer a company's worldwide hiring inactivity from the teaching result. The output measures only these added-event records during the declared closed window.
Step 1: Reuse the three requested dates. Step 2: Aggregate B events and retain dates without matches.
sql
-- Keep the same complete calendar and empty-count policy.
-- Change the daily population from company A to company B.The solution returns one row per calendar day with B's expected totals.
The solution returns one row per calendar day with B's expected totals. The three-day sum remains six, while the retained third day makes the time denominator explicit. If you later compute an average per requested day, it now uses three periods rather than two observed-event dates.
Acceptance should compare exact dates as well as totals. Three rows with a duplicated first day would satisfy a weak row-count check but still omit the intended period. The fixture's calendar keys are unique and its daily aggregation preserves that uniqueness through the join.
sql
WITH days(day) AS (VALUES ('2026-10-01'),('2026-10-02'),('2026-10-03')),
daily AS (SELECT day,SUM(added) AS n FROM observations WHERE company_id='B' GROUP BY day)
SELECT d.day,COALESCE(x.n,0) FROM days d LEFT JOIN daily x ON x.day=d.day ORDER BY d.day;In an interview, ask whether missing rows mean zero events or missing evidence.
In an interview, ask whether missing rows mean zero events or missing evidence. Then define the calendar, temporal type, timezone and coverage policy. A correct scaffold can align time, but it cannot manufacture reliable observations from an unreliable collection process.
The next lesson focuses on percentage denominators. Calendar completeness and eligible populations determine those denominators, so preserving requested periods and observation scope is part of the same reporting discipline.
Optional stretch — after the core task works: Add an explicit incomplete-coverage flag and show unknown rather than zero for an unobserved day.
Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.
Retain requested periods while separating complete-log zero from unknown collection coverage.