Practice: the whole floor

How to approach a question you cannot see the answer to

Every one of these starts the same way. Which tables hold the facts? Then: what is one row of the answer? — a customer, a month, a fitter. That is your group by. Then filter, then aggregate, then sort.

Build it in pieces and run it as you go. Join first and look at the rows. Add the grouping and check the counts are plausible. Add the filter last, and notice whether it belongs in where or in having.

select   <the thing>, <the numbers>
from     <the table one row of the answer comes from>
join     <what else the numbers need>
where    <row filters>
group by <the thing>
having   <group filters>
order by <the number that matters>

Traps this database is full of, deliberately

• Two customers have no city, so any <> on city loses them.
• One customer has never ordered, and one door has never been sold — an inner join hides both.
• Two orders are cancelled: almost every money question should exclude them.
• Four jobs have no fitter, and one fitter has no jobs — two different NULLs.
• order_items.unit_price is not doors.price: the money lives on the line, not in the catalogue.
• Joining order_items multiplies orders by their lines — count(*) stops counting orders.

Reading your own result

Before believing a number, check its scale. Eleven customers should give eleven rows, not forty. Total revenue across a grouping should add up to the same figure however you group it. If a "top spender" number is suspiciously round or suspiciously large, you are probably counting an order once per line.