Post #2687

Real‑world Data Analytics Questions with Answers 🔥 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!