Let’s practice end‑to‑end data thinking using this small dataset.
📊 Dataset: customer_orders
| order_id | customer_id | product | category | quantity | unit_price | order_date |
|----------|-------------|---------------|------------|----------|------------|-------------|
| 1 | 1001 | Laptop | Electronics| 2 | 75000 | 2025‑01‑10 |
| 2 | 1002 | Mouse | Electronics| 10 | 1500 | 2025‑01‑12 |
| 3 | 1003 | Chair | Furniture | 5 | 8000 | 2025‑01‑15 |
| 4 | 1001 | Keyboard | Electronics| 8 | 2500 | 2025‑01‑11 |
| 5 | 1004 | Desk | Furniture | 3 | 15000 | 2025‑01‑18 |
| 6 | 1002 | Monitor | Electronics| 4 | 25000 | 2025‑01‑20 |
| 7 | 1005 | Table | Furniture | 6 | 5000 | 2025‑01‑22 |
| 8 | 1003 | Webcam | Electronics| 12 | 3000 | 2025‑01‑14 |
1. Define what “data analysis” means in this context
• Data analysis means transforming raw orders into insights: what sells most, who the best customers are, and how revenue changes over time.
• You’d use SQL to query, Excel/Python to clean, and Power BI to visualize.
2. What are the key metrics you’d track for this business?
• Revenue = quantity × unit_price
• Order count and average order value (AOV)
• Top‑selling categories and best customers by revenue
3. Write a SQL query for total revenue by category
SELECT
category,
SUM(quantity * unit_price) AS total_revenue
FROM customer_orders
GROUP BY category;
4. How would you find repeat customers?
SELECT
customer_id,
COUNT(order_id) AS order_count,
SUM(quantity * unit_price) AS total_spent
FROM customer_orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;
• Customers with order_count > 1 are repeat buyers.
5. How would you detect “top customers”?
• Define “top” by total_spent or average order value:
– SUM(revenue) / COUNT(orders)
• Use Power BI/Excel to sort descending and highlight top 10%.
6. What would an outlier analysis look like?
• Compute min, max, average, standard deviation of revenue per order.
• Flag orders where:
– revenue > average + 2 * standard_deviation
• Check if such orders are errors or real big deals (e.g., enterprise purchase).
7. How would you report month‑on‑month growth?
• In SQL/Power BI:
– Group by YEAR(order_date) and MONTH(order_date)
– Compute revenue per month
– Then calculate:
▪ MoM % = (CurrentMonthRevenue − PreviousMonthRevenue) / PreviousMonthRevenue
8. How would you turn this into a dashboard?
• Page 1 – Overview: Cards for total revenue, total orders, AOV.
• Page 2 – Trends: Line chart for MoM revenue, bar chart for category split.
• Page 3 – Customers: Table for top 10 customers and repeat customers.
9. How would you handle dirty data (nulls, duplicates)?
• Pre‑check:
– COUNT(*) vs COUNT(customer_id) to spot missing customers.
• Clean:
– Drop or impute missing critical fields.
– Remove duplicate orders using DISTINCT or ROW_NUMBER().
10. How would you explain your findings to a non‑tech manager?
• Use simple language + visuals:
– “Our top product category is Electronics, contributing X% of revenue.”
– “N top customers account for M% of total sales.”
• Avoid formulas; focus on business impact: retention, profitability, growth.
Double Tap ❤️ For More!