Revisiting CASES mission & General SQL questions

Screen Link:

My Code:

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

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( <= 100 in the mathematical sense, this is equivalent to SUM( < 40. Perhaps you meant 40 < SUM( <= 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