Screen Link:
https://app.dataquest.io/m/189/intermediate-joins-in-sql/7/generating-columns-with-the-case-statement
My Code:
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?
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.
Your code:
SUM(iil.quantity) number_of_purchases
Solution code:
COUNT(i.invoice_id) number_of_purchases
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 (small_spender
, regular
, and 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.
Happy coding!
1 Like