Analyze Top 10 Items' Revenue Contribution by Category

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Amazon during technical interviews.

Question

sales

+----------+------------+---------+---------+------------+
| order_id | category   | item_id | revenue | order_date |
+----------+------------+---------+---------+------------+
| 1001     | Books      | B12     | 19.99   | 2023-07-01 |
| 1002     | Books      | B45     |  9.99   | 2023-07-02 |
| 1003     | Toys       | T88     | 29.99   | 2023-07-02 |
| 1004     | Toys       | T12     | 15.99   | 2023-07-03 |
| 1005     | Electronics| E33     |199.99   | 2023-07-03 |
+----------+------------+---------+---------+------------+

##### Scenario

SQL live-coding interview on product-sales dataset.

##### Question

Write a SQL query to return the top 10 items by total revenue within each product category. For every category, compute the percentage of category revenue that those top-10 items contribute. Explain the difference between normalization and denormalization and give scenarios for each. Outline the key steps of an ETL pipeline you would build for this dataset.

##### Hints

Think CTEs, ROW_NUMBER(), SUM() OVER, two-level aggregation.

PracHub · Accepted Answer

This question evaluates proficiency in data manipulation and analytical querying—aggregation, window functions, ranking and revenue attribution—alongside conceptual knowledge of normalization versus denormalization and ETL pipeline design.

Quick Overview

Quick Overview