Level 8 · Debug: too many holiday coupon orders

The query runs and returns rows that look like orders, so nothing warns you. The clue is in the ticket: orders without any coupon are in a coupon report.

Read the WHERE the way SQL does

AND binds tighter than OR, like * before +. The starter’s condition

WHERE coupon_code = 'HOLIDAY20'
  AND channel = 'web'
  OR channel = 'app'
  AND status = 'completed'

is evaluated as

WHERE (coupon_code = 'HOLIDAY20' AND channel = 'web')
   OR (channel = 'app' AND status = 'completed')

The second group has no coupon condition at all, so every completed app order qualifies: 3,966 rows in total. The line breaks and indentation suggest a different grouping, but the database ignores layout.

The fix is to make the channel choice a single condition:

WHERE coupon_code = 'HOLIDAY20'
  AND channel IN ('web', 'app')
  AND status = 'completed'

Now 148 orders come back, every one with HOLIDAY20, a web or app channel and a completed status.

How to spot this bug

• A column you filtered on shows values it should not. Here coupon_code has NULLs and status has cancelled orders. Scan each filtered column in the result.
• The count is out of proportion. HOLIDAY20 only runs December 1 to 20, and only 193 orders used it across all three years, so thousands is impossible.
• Any WHERE with both AND and OR and no parentheses deserves a second look, even if the result seems right.

Common mistakes

• Fixing it by moving lines around. Order and indentation do not change precedence; only parentheses or IN do.
• Wrapping the wrong part, such as (coupon_code = 'HOLIDAY20' AND channel = 'web') OR .... That spells out the bug instead of fixing it.
• Checking only the row count after the fix. Also scan the result once more: every row should now show HOLIDAY20, a web or app channel and a completed status. If one column still shows an unexpected value, a condition is still in the wrong group.

In SQL Server

AND binds before OR there too, so the starter has the same bug in SQL Server, MySQL, Postgres and every other database. The fix is identical, and IN is standard SQL everywhere:

WHERE coupon_code = 'HOLIDAY20'
  AND channel IN ('web', 'app')
  AND status = 'completed';