Back to questions
This is the same question as problem #12 in the SQL Chapter of Ace the Data Science Interview!
Assume you're given a table containing data on Amazon customers and their spending on products in different category, write a query to identify the top two highest-grossing products within each category in the year 2022. The output should include 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|
Within the "appliance" category, the top two highest-grossing products are "refrigerator" and "washing machine."
In the "electronics" category, the top two highest-grossing products are "vacuum" and "wireless headset."
The dataset you are querying against may have different input & output - this is just an example!
The first step to solving this Amazon question is writing Ace the Data Science Interview an Amazon review if you liked the book! 😉
Step 1: Find the highest-grossing products by category and product
To identify the highest-grossing products, we need to calculate the total spend for each category and product. We should also filter the transactions to only include those from the year 2022.
|electronics||3.5mm headphone jack||7.99|
This query provides a result that shows the total spend for each category and product combination.
Step 2: Rank the products by total spend within each category
To determine the ranking of products based on total spend within each category, we can utilize the window function within the same query.
|electronics||3.5mm headphone jack||7.99||4|
The result of this query includes the category, product, total spend, and the ranking of products within each category based on their total spend.
Step 3: Filter for the top two highest-grossing products within each category
Since we're interested in the top two highest-grossing products only, we can use the previous query as a CTE and filter for products with a ranking less than or equal to 2.
Solution #2: Using Subquery
Here's an alternative approach using subquery.