ISA-014 — Top Selling Products
🟢 Beginner
⏱ 25 Minutes
⭐ 20 Points
🔓 Free
💻 SQL | PySpark
🏢 Retail Analytics
🎯 GROUP BY
🎯 ORDER BY
🎯 LIMIT
Business Context
A retail company wants to identify its highest revenue-generating products.
Management uses this information for:
- Inventory planning
- Marketing campaigns
- Product promotions
- Revenue forecasting
The analytics team has been asked to generate a ranked list of products based on total revenue generated.
Business Impact
Knowing the top-performing products helps businesses:
- Increase sales
- Optimize stock levels
- Improve product strategy
- Focus marketing efforts
This is one of the most frequently requested business reports.
Dataset
products
products
| product_id | product_name |
|---|---|
| P101 | Laptop |
| P102 | Mobile |
| P103 | Headphones |
| P104 | Tablet |
order_items
order_items
| order_id | product_id | amount |
|---|---|---|
| O001 | P101 | 1000 |
| O002 | P101 | 1200 |
| O003 | P102 | 800 |
| O004 | P103 | 200 |
| O005 | P102 | 700 |
| O006 | P104 | 500 |
| O007 | P104 | 400 |
Task
Calculate total revenue for each product.
Return only the Top 2 products by revenue.
Expected Output
expected_output
| product_name | total_revenue |
|---|---|
| Laptop | 2200 |
| Mobile | 1500 |
Constraints
- Join products and order_items.
- Aggregate revenue at product level.
- Sort by revenue descending.
- Return only the top 2 products.
Supported Languages
✅ SQL
✅ PySpark
Data Engineering Pattern
Top-N Analytics Pattern
A common reporting pattern used in:
- Executive dashboards
- Sales analytics
- Product performance reports
- Revenue leaderboards
Concepts Tested
- INNER JOIN
- GROUP BY
- SUM
- ORDER BY DESC
- LIMIT
Hint
Aggregate revenue first.
Then sort the results from highest to lowest.
Finally return only the first two rows.
Solution
🔒 Premium Solution
Premium members receive:
- SQL Solution
- PySpark Solution
- Step-by-Step Explanation
- Ranking Walkthrough
- Alternative Approaches
Notebook Workspace
Cell 1Dataset: products
⏱ 15:00
Loading...
Run Results
✓ Workspace Ready
Rows Returned: --
Execution Time: --
Engine: Coming Soon
Execution Engine Coming Soon