190-8 | Why did the anser took a much longer/complex road

Q&A DQ Courses
, ,
#1

Hi!
I just solved 190-8. I’ve been forcing myself to write the answers completely off-platform on Notepad++, then bringing them back and running them. I want to learn without relying too much on iterative testing.

Anyway, so I solve this one on the first try, and I decided to check the answer to see any differences, and I see it took a much longer and complex road. I know the missions are open-ended and what matters is the end-result, but I fear that maybe there’s something I’m missing.

My question is specifically about this temporary view in the answer:

country_best_customer AS
    (
     SELECT
        cmp.country,
        cmp.max_purchase,
        (
         SELECT ccp.customer_id
         FROM customer_country_purchases ccp
         WHERE ccp.country = cmp.country
               AND cmp.max_purchase = ccp.total_purchases
        ) customer_id
     FROM country_max_purchase cmp
    )

Why is it necessary? I feel the second temporary view that calculates the MAX spend from each country already works because it gives you the customer_name associated with it if you add it to the SELECT statement.

2 Likes
#2

Well spotted, Juan.

The first two CTEs are enough to find the answer. I can’t tell what your answer looks like from your explanation, so it’s hard to be sure that you’re not missing anything, but I think you aren’t.

Here’s one way to make it work using only the first two CTEs (with a small modification to get the customers’ names right from the get-go):

WITH customer_country_purchases AS

  (SELECT c.first_name || " " || c.last_name AS customer_name,
          c.country,
          SUM(i.total) total_purchases
     FROM invoice AS i
    INNER JOIN customer AS c ON i.customer_id = c.customer_id
    GROUP BY 1, 2),
    
     country_max_purchase AS
  (SELECT country,
          MAX(total_purchases) AS max_purchase
     FROM customer_country_purchases
    GROUP BY 1)
   
SELECT ccp.country,
       ccp.customer_name,
       ccp.total_purchases AS total_purchased
  FROM customer_country_purchases AS ccp
 INNER JOIN country_max_purchase AS cmp
    ON ccp.country = cmp.country
       AND ccp.total_purchases = cmp.max_purchase
ORDER BY 1;

Note, however, that both these solutions carry a hidden assumption: that each customer belongs to only one country. This is true, but it’s something to be mindful of.

3 Likes
#3

I had the same feeling when I’ve checked the answer. What am I missing? There are so many queries and I have basically only two:

WITH purchases AS (
SELECT 
    c.country, 
    c.first_name || ' ' || c.last_name customer_name,
    SUM(i.total) total_purchased
FROM customer c 
INNER JOIN invoice i ON (i.customer_id = c.customer_id)
GROUP BY c.customer_id)

SELECT
    * 
FROM purchases 
GROUP BY country
HAVING total_purchased = MAX(total_purchased)
ORDER BY country

UPD: I think maybe I’ve got the point, there were no “HAVING” in the course and I had quite a SQL experience…

1 Like
#4

Hey, Maksym.

Your query actually has at least one problem. I’ll copy and paste my reply to another topic that fits perfectly here:

Your solution has insidious issues that aren’t easy to spot, it works here because of some specific circumstances.

The issue is that you’re selecting columns without grouping by them. In a query where you use GROUP BY , every column must either by in the GROUP BY clause or an aggregate function must be used on it.

For more details please read this from the third quote onwards.

1 Like
#5

Hey Bruno
Do I have the same issue as Maksym here? I feel like the answers are a little bit different.
Also still don’t get how to make code look like code in this platform :confused:

 >    WITH 
>         total_purchased AS (
>             SELECT 
>                 customer_id,
>                 SUM(Total) as total_purchases
>             FROM invoice
>             GROUP BY customer_id
>                     )
>                    
>     SELECT
>                 c.country,
>                 (c.first_name || " " || c.last_name) AS customer_name,
>                 MAX(t_p.total_purchases) as total_purchased
>      FROM customer AS c
>      INNER JOIN total_purchased AS t_p
>      ON c.customer_id=t_p.customer_id
>      GROUP BY country 
>      ORDER BY country
1 Like
#6

Yes.

Encase your code in-between two pairs of three backticks, like this:

```
CODE GOES HERE
```
1 Like
#7

Hello @Bruno:

I tried to skip one of the temporary view in the solution and wrote my query in simpler way and achieved the desired result. Here is my code -

WITH
customer_country_purchases AS
(
SELECT
i.customer_id,
c.country,
SUM(i.total) total_purchases
FROM invoice i
INNER JOIN customer c ON i.customer_id = c.customer_id
GROUP BY 1, 2
),
country_max_purchase AS
(
SELECT
country,
MAX(total_purchases) max_purchase,
customer_id
FROM customer_country_purchases
GROUP BY 1
)

SELECT
cmp.country country,
c.first_name || " " || c.last_name customer_name,
cmp.max_purchase total_purchased
FROM customer c
INNER JOIN country_max_purchase cmp ON cmp.customer_id = c.customer_id
ORDER BY 1 ASC

Let me know your thoughts on this.

Thanks in advance for your feedback.

Best
K!

1 Like