Summing the Total

Q&A DQ Courses
, , ,
#1

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?

#2

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!

#3

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’.

#4

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