This is the same question as problem #12 in the SQL Chapter of Ace the Data Science Interview!
Assume you are given the table below containing information on Amazon customers and their spend on products belonging to various categories. Identify the top two highest-grossing products within each category in 2022. Output the category, product, and total spend.
|appliance||washing machine||123||219.80||03/02/2022 12:00:00|
|electronics||wireless headset||156||249.90||07/08/2022 12:00:00|
The first step to solving this Amazon question is writing Ace the Data Science Interview an Amazon review if you liked the book!
To find the highest-grossing products, we must find the total spend by category and product. Note that we must filter by transactions in 2022.
The output represents the total spend by category (electronics, appliance) and product.
Then, we reuse the query as a CTE or subquery (in this case, we are using a CTE) and utilize the window function to calculate the ranking by total spend, partition by category and order by the total spend in descending order.
Finally, we use this result and filter for a rank less than or equal to 2 as the question asks for top two highest-grossing products only.
Solution #1: Using CTE
A CTE is a temporary data set to be used as part of a query and it exists during the entire query session. A subquery is a nested query. It’s a query within a query and unlike CTE, it can be used within that query only. Read here and here for more details.
Both methods give the same output and perform fairly similarly. Differences are CTE is reusable during the entire session and more readable, whereas subquery can be used in FROM and WHERE clauses and can act as a column with a single value. We share more resources here (1, 2, 3 on their use cases.
Solution #2: Using Subquery