The mistakes interviewers see most

Every exercise in this lesson is a query a candidate actually wrote in an interview (on Wadi Market's shop data). Each one runs, or nearly runs, and each one looks reasonable. Fix the query, then practise the one-sentence explanation in the exercise's solution: that sentence is what you would say if the interviewer asked “why did you write it that way?”.

The six mistakes, in the order of the exercises:

1. *COUNT() after a LEFT JOIN.** The unmatched row counts as 1, so ‘zero’ never appears.
2. WHERE instead of HAVING. WHERE runs before grouping, so it cannot see aggregates.
3. BETWEEN on timestamps. BETWEEN '2025-12-01' AND '2025-12-31' stops at midnight at the start of the 31st.
4. DISTINCT to hide a fan-out. A join multiplied rows; SUM(DISTINCT …) then drops genuinely equal values, so the total is wrong in a new way.
5. Averages at the wrong grain. Averaging order lines is not the average order value.
6. LIMIT for top-N per group. LIMIT applies to the whole result, not to each group.