SELECT c.first_name ||' '|| c.last_name customer_name, SUM(iil.quantity) number_of_purchases, SUM(iil.unit_price * iil.quantity) total_spent, CASE WHEN SUM(iil.quantity) < 40 THEN 'small_spender' WHEN SUM(iil.quantity) > 100 THEN 'big spender' WHEN SUM(iil.quantity) > 40 & SUM(iil.quantity) < 100 THEN 'regular' END AS customer_category FROM customer c LEFT JOIN ( SELECT il.*, i.customer_id, i.total, i.invoice_id FROM invoice i LEFT JOIN invoice_line il ON il.invoice_id = i.invoice_id) iil ON iil.customer_id = c.customer_id GROUP BY c.customer_id ORDER BY customer_name
What I expected to happen:
I expected the output to be the accepted answer on Dataquest but it is not.
What actually happened:
Instead, it tells me the answer doesn't look right
When I input the Dataquest answer, however, that answer doesn’t make sense to me. The total_spent is too large when you consider the number of purchases. Could someone please explain where I am going wrong?