Requirement Change Drills

This drill simulates common follow-up behavior where requirements change mid-solution.

Start with: monthly completed-order revenue.
Change 1: only first-time customers.
Change 2: weekly grain instead of monthly.
Change 3: exclude refunded orders.
Change 4: include tie-handled top 3 segments by revenue each week.

For each change:

1. restate requirement in one sentence
2. identify exact CTE/filter impacted
3. show updated SQL fragment
4. run one validation check

Change 3 is order-level, not item-level: "exclude refunded orders" drops the whole order when any of its items was returned (the NOT EXISTS anti-join below), not just the returned line. On this dataset that removes orders 9001, 9004, 9008, 9012, 9017, and 9019 entirely — so customer 3 disappears (both of their orders contain a returned item) and customer 1's first-time order shifts from January (order 9001 contains a returned item) to February. If the interviewer instead wants returns netted line-by-line, that is a different requirement, so ask.

• correctness under changing constraints (40%)
• clarity of delta explanation (25%)
• validation quality (20%)
• time control and composure (15%)