Analyze Top 10 Items' Revenue Contribution by Category
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
Top 10 Items by Category
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. Return one row per top item in each category with its total revenue and rank, plus category totals and the percent of category revenue contributed by the top 10 items (repeated per row within the category).
Tables
sales(order_id INTEGER, category VARCHAR(50), item_id VARCHAR(20), revenue DECIMAL(10,2), order_date DATE)
Hints
- Aggregate revenue by category and item first.
- Use ROW_NUMBER() partitioned by category to rank items.
Normalization vs Denormalization
Explain the difference between normalization and denormalization and give concise scenarios for when to use each.
Tables
sales(order_id INTEGER, category VARCHAR(50), item_id VARCHAR(20), revenue DECIMAL(10,2), order_date DATE)
Hints
- Keep definitions short and contrast goals (integrity vs performance).
- Provide a concrete example for each.
Outline ETL Pipeline Steps
Outline the key steps of an ETL pipeline you would build for this dataset, covering extract, transform, and load, including quality checks and publishing aggregates.
Tables
sales(order_id INTEGER, category VARCHAR(50), item_id VARCHAR(20), revenue DECIMAL(10,2), order_date DATE)
Hints
- Think in phases: extract, validate, enrich, transform, load, publish, orchestrate.
- Include quality checks, deduplication, and partitioning strategies.