SELECT c.first_name ||' '|| c.last_name customer_name,
SUM(iil.unit_price * iil.quantity) total_spent,
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'
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?
Your solution looks good except for that tricky
number_of_purchases and a slight difference in
total_spent due to rounding errors. I wouldn’t worry too much about the rounding errors but we should take a deeper look at why your
number_of_purchases is so different (much higher) than the solution’s.
Comparing the two, we see that the difference is: “number of tracks purchased” vs “number of times a customer made a purchase.” We want the second one. Keep in mind, it’s possible to purchase more than one track at a time.
I also spotted another small problem with your code with respect to
customer_category. We want to label customers (
big_spender) based on how much the spend, not on how many tracks they purchased. I noticed this because
Diego Gutiérrez is labelled as
regular by your code but he should be
small_spender because his
total_spent is under $40.
Let me know if this helps or if I can clarify anything else for you!
Thank you so much! I have fixed my code. I also realised I was needlessly complicating the task because I assumed they were asking for ‘number of tracks purchased’.
Nicely done, congrats! Just know, this is very common with SQL: making it more complicated than it needs to be! It’s all a learning process. Just keep going and experiment with the code until it makes sense.