Patterns agents get wrong

Four quiet failures

order_items contains 15 byte-identical duplicate rows. SELECT DISTINCT * removes them, avoiding 7,278.04 of duplicate line revenue. orders contains 12 repeated IDs with different status and _loaded_at; keep the latest with QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY _loaded_at DESC) = 1. DISTINCT would keep both.

A GROUP BY omits a category-week with no sales. Build the 53-week date spine from dates, cross it with categories, and left join revenue so 128 missing cells become zero. Use >= start AND < end for timestamps. For FX, date alone joins each row to five currency pairs. Match the date, currency_code, and the USD target before summing.

-- One matching FX rate for the order's date, source currency, and USD target.
ON CAST(o.ordered_at AS DATE) = f.rate_date
AND o.currency_code = f.from_currency
AND f.to_currency = 'USD'

The correctly converted 2023 figure is 258,645.94 USD. A date-only join turns 264,926.21 of unconverted line revenue into 1,324,631.05, exactly five times too high.

Your task

Fix queries/weekly-category.sql to use the category-week spine. Confirm it returns 424 rows, with 128 zero-revenue cells. Then explain which duplicate problem DISTINCT fixes and which needs QUALIFY.

Check your understanding

• order_items and orders each have duplicate rows, but they need different fixes. What is the fix for each, and why does the other one not work for the other table?
• A weekly revenue chart has a gap in the middle. Is the gap more likely a missing-data problem or a missing-row problem, and what fixes it?
• Why does the casted date-only join to fx_rates multiply revenue by exactly 5 rather than giving a wrong-but-plausible number, and what is a separate timestamp/date mistake?

Do it with your agent

Say next lesson, make the spine change yourself, then say review my work. The review should check 53 weeks, 424 rows, 128 zeroes, and the separate diagnosis for exact duplicates and changed-status resends.