I have mixed feelings. I did the exercise slightly modifying the previous task. Everything worked.
WITH
customers AS
(
SELECT *
FROM customer
),
total_purchases_inv AS
(
SELECT
i.customer_id,
SUM(i.total) AS sum_tot
FROM invoice AS i
GROUP BY i.customer_id
)
SELECT c.country AS country,
c.first_name || " " || c.last_name AS customer_name,
MAX(tpi.sum_tot) AS total_purchased
FROM customers AS c
INNER JOIN total_purchases_inv AS tpi ON tpi.customer_id = c.customer_id
GROUP BY 1
ORDER BY 1 ASC;
I realise that this is probably not the best solution, as I was supposed to practice more complex versions of the code. But since mine works - and is also shorter (22 lines), why extend it( DQ answer: 38 lines?
When I read the task hints, I thought: I have no idea what the logic is behind them.
So I recalled myself my previous lessons and end up with this solution mentioned above.
Just finished analysing DQ answer (posted below):
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
FROM customer_country_purchases
GROUP BY 1
),
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
)
SELECT
cbc.country country,
c.first_name || " " || c.last_name customer_name,
cbc.max_purchase total_purchased
FROM customer c
INNER JOIN country_best_customer cbc ON cbc.customer_id = c.customer_id
ORDER BY 1 ASC
I like the part WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases ) customer_id
. I didn’t know that it’s allowed to use multiple =
Besides of that, the DQ answer seems to be far from zen. What’s more, my approach seems to be more related to previous exercises.
I will be grateful for your comments.