Going fast! #DataquestChallenge Premium Annual Offer:
500 get 50% & the next 1000 get 40% off.
GET OFFER CODE

Possible pitfall for this alternative solution?

A simpler solution to the problem if the tagged lesson

Intermediate SQL for Data Analysis →
Building and Organizing Complex Queries → Lesson 8

I am wondering if I’m missing any pitfall

WITH tot_cus AS(
    SELECT c.customer_id id, first_name || " " || last_name name,
    SUM(i.total) tot
    FROM customer c
    INNER JOIN invoice i ON c.customer_id = i.customer_id
    GROUP BY 1
    )

SELECT c.country, tc.name customer_name, MAX(tc.tot) total_purchased
FROM tot_cus tc
INNER JOIN customer c ON tc.id = c.customer_id
GROUP BY 1

Cheers and happy learning!

Your solution works because of a SQLite quirk, but it wouldn’t work in many other DBMSs. A problem is that you have columns that aren’t arguments to any aggregate function and are not in GROUP BY.

You can read more about this here.

Good job on getting the sense that there could something wrong with your answer.

Brilliant,

Thank you very much Bruno!

1 Like