Introduction: Company में SQL कैसे use होता है
Data Scientist का 70% काम SQL है। Excel नहीं, Python नहीं। पहले Data निकालना है।
Rule: Business Problem → SQL Query → Insight → Decision
आज हम 5 real case study करेंगे। हर case में Table, Problem, Query, और Insight दूंगा।
Dataset: ecommerce, gaming, ott, food_delivery, hr ये 5 table मानेंगे।
Case Study 1: E-commerce – Amazon/Flipkart –
Table: orders(order_id, customer_id, order_date, product_id, amount, category)
Q1. Monthly Revenue Trend निकालो
SELECT
DATE_FORMAT(order_date, ‘%Y-%m’) as month,
SUM(amount) as revenue
FROM orders
GROUP BY 1
ORDER BY 1;
Insight: Diwali में revenue 3x हुआ। उसी महीने ads बढ़ाओ।
Q2. हर Category के Top 3 Products
WITH ranked AS (
SELECT category, product_id, SUM(amount) as total_sales,
RANK() OVER(PARTITION BY category ORDER BY SUM(amount) DESC) as rnk
FROM orders
GROUP BY 1,2
)
SELECT * FROM ranked WHERE rnk <= 3;
Insight: Electronics में iPhone #1 है। उसका stock बढ़ाओ।
Q3. Customer Retention Rate
पिछले महीने आए थे और इस महीने भी आए।
WITH monthly_customers AS (
SELECT DISTINCT customer_id, DATE_FORMAT(order_date, ‘%Y-%m’) as month
FROM orders
)
SELECT COUNT(DISTINCT a.customer_id) / COUNT(DISTINCT b.customer_id) * 100 as retention
FROM monthly_customers a
JOIN monthly_customers b ON a.customer_id = b.customer_id
AND a.month = DATE_ADD(b.month, INTERVAL 1 MONTH);
Case Study 2: Gaming – BGMI/Free Fire –
Table: matches(player_id, match_date, kills, deaths, result)
Q1. DAU, MAU, Retention निकालो
— DAU
SELECT DATE(match_date) as day, COUNT(DISTINCT player_id) as DAU
FROM matches GROUP BY 1;
— 7 Day Retention
SELECT COUNT(DISTINCT a.player_id) / COUNT(DISTINCT b.player_id) * 100
FROM matches a JOIN matches b
ON a.player_id = b.player_id
AND DATEDIFF(a.match_date, b.match_date) = 7;
Q2. Top 10 Players by K/D Ratio
SELECT player_id,
SUM(kills)/SUM(deaths) as kd,
RANK() OVER(ORDER BY SUM(kills)/SUM(deaths) DESC) as rnk
FROM matches GROUP BY 1;
Insight: K/D > 3 वाले players को influencer बनाओ।
Case Study 3: OTT – Netflix/Prime –
Table: watch_history(user_id, content_id, watch_date, watch_duration)
Q1. सबसे ज्यादा देखी जाने वाली Series
SELECT content_id, SUM(watch_duration) as total_watch_time
FROM watch_history
GROUP BY 1 ORDER BY 2 DESC LIMIT 5;
Q2. Churn Prediction – 30 दिन से login नहीं किया
SELECT user_id
FROM users u
LEFT JOIN watch_history w ON u.user_id = w.user_id
GROUP BY 1
HAVING MAX(w.watch_date) < DATE_SUB(CURDATE(), INTERVAL 30 DAY);
Insight: इन्हें “We miss you” वाला mail भेजो + discount दो।
Q3. Binge Watchers
एक दिन में 3 घंटे से ज्यादा देखने वाले
SELECT user_id, DATE(watch_date) as day, SUM(watch_duration)
FROM watch_history
GROUP BY 1,2 HAVING SUM(watch_duration) > 180;
Case Study 4: Food Delivery – Swiggy/Zomato –
Table: orders(order_id, restaurant_id, order_time, delivery_time)
Q1. Peak Order Hours
SELECT HOUR(order_time) as hour, COUNT(*) as total_orders
FROM orders GROUP BY 1 ORDER BY 2 DESC;
Insight: 8 PM सबसे peak है। उसी समय delivery boys ज्यादा रखो।
Q2. Avg Delivery Time by Restaurant
SELECT restaurant_id,
AVG(TIMESTAMPDIFF(MINUTE, order_time, delivery_time)) as avg_delivery
FROM orders GROUP BY 1;
Case Study 5: HR Analytics – Company Employees –
Table: employees(emp_id, dept, salary, join_date, exit_date)
Q1. Department wise Attrition Rate
SELECT dept,
COUNT(CASE WHEN exit_date IS NOT NULL THEN 1 END) / COUNT(*) * 100 as attrition
FROM employees GROUP BY 1;
Q2. Salary Percentile
SELECT emp_id, salary,
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY salary) * 100 as percentile
FROM employees;
Part 5: Key Patterns जो हर जगह काम आएंगे –
हर Case Study में यही 4 pattern घूम रहे थे:
1.Time Series: GROUP BY DATE() – Trend निकालना
2.Ranking: RANK() OVER(PARTITION BY) – Top N निकालना
3.Retention/Churn: LAG() + JOIN on date – Customer आया या गया
4.Segmentation: CASE WHEN – Users को group करना
Formula Sheet:
Growth = (This Month – Last Month) / Last Month * 100
Retention = Returning Users / Total Users * 100
Churn = 1 – Retention
Practice Project –
अपना खुद का Project बनाओ:
Dataset: Kaggle से “E-commerce Dataset” download करो
1.Task: Monthly Revenue Dashboard
2.Top 5 Products
3. Customer Cohort Analysis
GitHub पर डाल दो। Resume में लिख दो। Interview पक्का।
Conclusion – 200 words
क्या सीखा?
SQL सिर्फ SELECT * नहीं है। Business सवाल का जवाब है।
Interview Tip: जब भी सवाल आए तो पहले Table का structure मांगो, फिर Query लिखो।
अगला Chapter 10: “SQL Optimization & Indexing”
1 करोड़ row वाली table 2 सेकंड में कैसे चलाएं।