ISA-013 — Product Revenue Analysis
🟢 Beginner
⏱ 25 Minutes
⭐ 20 Points
🔓 Free
💻 SQL | PySpark
🏢 Retail Analytics
🎯 INNER JOIN
🎯 GROUP BY
Business Context
A retail company sells products through an online platform.
Management wants to understand which products generate the most revenue.
Product information is stored separately from sales transactions.
The analytics team has been asked to calculate total revenue generated by each product.
Business Impact
Product revenue analysis helps:
- Identify top-performing products
- Optimize inventory planning
- Improve pricing decisions
- Support executive reporting
This type of analysis is commonly used in BI dashboards and sales reporting.
Dataset
products
| product_id | product_name |
|---|---|
| P101 | Laptop |
| P102 | Mobile |
| P103 | Headphones |
order_items
| order_id | product_id | amount |
|---|---|---|
| O001 | P101 | 1000 |
| O002 | P101 | 1200 |
| O003 | P102 | 800 |
| O004 | P103 | 200 |
| O005 | P102 | 700 |
Task
Calculate total revenue generated by each product.
Return:
- product_name
- total_revenue
Expected Output
| product_name | total_revenue |
|---|---|
| Laptop | 2200 |
| Mobile | 1500 |
| Headphones | 200 |
Constraints
- Join products with order_items using product_id.
- Aggregate revenue at product level.
- Return one row per product.
Supported Languages
✅ SQL
✅ PySpark
Data Engineering Pattern
Dimensional Aggregation Pattern
A dimension table provides descriptive attributes.
A transaction table provides measurable facts.
The two are combined to produce business metrics.
Concepts Tested
- INNER JOIN
- GROUP BY
- SUM
- Aggregation
- Revenue Analytics
Hint
First combine products and transactions.
Then aggregate revenue by product.
Solution
🔒 Premium Solution
Premium members receive:
- SQL Solution
- PySpark Solution
- Step-by-Step Explanation
- Join Visualization
- Aggregation Walkthrough
- Alternative Approaches