Solution for 190-8 “Challenge: Each Country’s Best Customer” seems overly complicated?

Q&A DQ Courses
#1

Well, it has a very simple solution as given below. I don’t understand the need for the complicated query given in solution.

WITH 
    customer_country_purchases AS 
    (SELECT   
     c.customer_id,  
     c.country AS country, 
     c.first_name || " " || c.last_name AS customer_name,
     ROUND(SUM(i.total),2) AS total_purchase 
  FROM customer AS c
                   INNER JOIN invoice AS i ON c.customer_id = i.customer_id
 GROUP BY 1, 2
 ORDER BY 2),
    
    max_purchases AS
    (SELECT
          ccp.customer_id,
          ccp.country,   
          ccp.customer_name,
          MAX(ccp.total_purchase) AS max_purchase
       FROM customer_country_purchases AS ccp
      GROUP BY 2
    )
SELECT
     mp.country AS country,
     mp.customer_name AS customer_name,
     mp.max_purchase AS total_purchased
  FROM max_purchases AS mp
 ORDER BY 1;

My Code: 


Solution for 190-8 “Challenge: Each Country’s Best Customer” seems overly complicated?

What I expected to happen:

What actually happened:

Replace this line with the output/error
#2

Hi @saquibmehmood1, your question has been well discussed in a similar post here. Please refer to it and you will appreciate why the suggested solution, though a bit complex, is appropriate and will work across other DB platforms.

Your query works because were are dealing with the SQLite database. Other DBMSs (e.g., SQL Server) will require that all columns specified in addition to an aggregate function in the SELECT clause must be used in the GROUP BY clause.