Revisiting CASES mission & General SQL questions

Screen Link:
https://app.dataquest.io/c/46/m/189/intermediate-joins-in-sql/7/revisiting-case

My Code:

SELECT
    (c.first_name || " " || c.last_name) AS customer_name,
    COUNT(i.invoice_id) AS number_of_purchases,
    SUM(i.total) AS total_spent,
    CASE
        WHEN SUM(i.total) <= 40 THEN "small spender"
        WHEN 40 > SUM(i.total) <= 100 THEN "regular"
        WHEN SUM(i.total) > 100 THEN "big spender"
        END 
        AS customer_category
  FROM customer c
 INNER JOIN invoice i ON i.customer_ID = c.customer_id
 GROUP BY 1 ORDER BY 1

What I expected to happen:
When using the comparison in the CASE function above, my code was missing categorising customers as big spender which had a higher spent than 100. Why is this the case?

By looking at other threads on this threads on this topic, for me it was intuitive that I needed also an extra table to count the number of purchases. In this case, it was the invoice table. At the same time, I had problems in thinking the problem through. I could not understand why my logic was making me think that I need to have to join to a subquery, or that I needed to have a join under another join. Why was I thinking like this?

Is it because I didn’t understand the joins? Can someone direct me to where I can practice and read a bit more?

Thank you for your time

SQLite is weird… Thinking about 40 > SUM(i.total) <= 100 in the mathematical sense, this is equivalent to SUM(i.total) < 40. Perhaps you meant 40 < SUM(i.total) <= 100.

Still, this isn’t valid SQL syntax in general. SQLite runs it, but it’s unclear what it is doing as the result does not conform with the mathematics. I tried it in a couple of other databases and they yield errors.

Suggestion: do not use chained inequalities, SQL isn’t equipped for it.

1 Like