Solution for Complex Queries Lesson

Q&A DQ Courses
, , , ,
#1

Hello, I wrote my solution for the screen:
Screen Link:

My Code: 

WITH 
   purchases_country AS
    (
    SELECT
        i.customer_id customer_id,
        c.country country,
        SUM(i.total) total_purchases
    FROM invoice i
    INNER JOIN customer c ON c.customer_id = i.customer_id
    GROUP BY i.customer_id, c.country
    ),
    country_max_purchase AS
    (
    SELECT
        country,
        customer_id,
        MAX(total_purchases) max_purchase
    FROM purchases_country
    GROUP by country
    )
    
SELECT 
    cmp.country country,
    c.first_name || " " || c.last_name customer_name,
    round(cmp.max_purchase, 2) total_purchased
    
FROM customer c
INNER JOIN country_max_purchase cmp ON cmp.customer_id = c.customer_id

ORDER BY country
;

The result I got is similar to the given one. For answer system “round” function must be removed.
I’m not claiming that my solution is the best, it’s just my own work and I’m so happy that I was able to write it, complex queries are still hard for me.

2 Likes