SQL FOR DATA
SCIENCE

Data Science mein SQL ka use:

1. DATA EXTRACTION ✓ Raw data nikalo database se
2. DATA CLEANING ✓ Missing values handle, duplicates hatao
3. AGGREGATION ✓ Summary statistics nikalo (group by)
4. FEATURE ENGINEERING ✓ New columns banao (CASE, calculations)
5. A/B TESTING ✓ Groups compare karo SQL se
6. REPORTING ✓ Dashboards ke liye queries likho

Production mein 70% data scientist ka kaam SQL hai —
Python sirf 30%!

-- Basic data extraction
SELECT name, course, marks
FROM students
WHERE marks > 70
ORDER BY marks DESC
LIMIT 10;

-- Multiple conditions
SELECT name, course, marks
FROM students
WHERE course = 'Python' AND marks >= 80
ORDER BY name;

-- LIKE pattern matching
SELECT name, email
FROM students
WHERE email LIKE '%@gmail.com';

-- IN operator
SELECT name, course
FROM students
WHERE course IN ('Python', 'SQL', 'ML');

-- Basic aggregation
SELECT 
 COUNT(*) as total_students,
 AVG(marks) as avg_marks,
 MAX(marks) as top_marks,
 MIN(marks) as lowest_marks
FROM students;

-- Group by single column
SELECT course, 
 COUNT(*) as students,
 AVG(marks) as avg_marks,
 MAX(marks) as top_marks
FROM students
GROUP BY course
ORDER BY avg_marks DESC;

-- Group by multiple columns
SELECT course, city,
 COUNT(*) as students,
 AVG(marks) as avg_marks
FROM students
GROUP BY course, city
ORDER BY course, avg_marks DESC;

-- HAVING — filtered aggregation
SELECT course, 
 COUNT(*) as students,
 AVG(marks) as avg_marks
FROM students
GROUP BY course
HAVING AVG(marks) > 80
ORDER BY avg_marks DESC;

-- INNER JOIN — sirf matching rows
SELECT s.name, s.marks, c.course_name, c.duration
FROM students s
INNER JOIN courses c ON s.course_id = c.id;

-- LEFT JOIN — sab left ki rows
SELECT s.name, s.marks, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.id;

-- RIGHT JOIN — sab right ki rows
SELECT s.name, s.marks, c.course_name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.id;

-- Multiple JOINs
SELECT s.name, c.course_name, e.enroll_date, p.amount
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id
INNER JOIN payments p ON e.id = p.enrollment_id
WHERE e.enroll_date >= '2024-01-01';

-- Self JOIN
SELECT a.name as student1, b.name as student2, a.course
FROM students a
INNER JOIN students b ON a.course = b.course AND a.id != b.id;

-- RANK — course wise ranking
SELECT name, course, marks,
 RANK() OVER (PARTITION BY course ORDER BY marks DESC) as rank,
 DENSE_RANK() OVER (PARTITION BY course ORDER BY marks DESC) as dense_rank,
 ROW_NUMBER() OVER (PARTITION BY course ORDER BY marks DESC) as row_num
FROM students;

-- Running totals aur moving averages
SELECT name, course, marks,
 SUM(marks) OVER (PARTITION BY course ORDER BY marks) as running_total,
 AVG(marks) OVER (PARTITION BY course ORDER BY marks 
 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3
FROM students;

-- Course average ke saath comparison
SELECT name, course, marks,
 RANK() OVER (PARTITION BY course ORDER BY marks DESC) as rank,
 AVG(marks) OVER (PARTITION BY course) as course_avg,
 marks - AVG(marks) OVER (PARTITION BY course) as diff_from_avg
FROM students;

-- LAG — previous row ka value
SELECT name, course, marks,
 LAG(marks, 1) OVER (PARTITION BY course ORDER BY marks) as prev_marks,
 marks - LAG(marks, 1) OVER (PARTITION BY course ORDER BY marks) as diff
FROM students;

-- LEAD — next row ka value
SELECT name, course, marks,
 LEAD(marks, 1) OVER (PARTITION BY course ORDER BY marks) as next_marks
FROM students;

-- Monthly revenue analysis
SELECT DATE_FORMAT(order_date, '%Y-%m') as month,
 COUNT(*) as orders,
 SUM(amount) as revenue,
 AVG(amount) as avg_order_value
FROM orders
GROUP BY month
ORDER BY month;

-- Year-over-year comparison
SELECT YEAR(order_date) as year,
 MONTH(order_date) as month,
 SUM(amount) as revenue,
 LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date)) as prev_year_revenue,
 ROUND(
 (SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date))) 
 / LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date)) * 100, 2
 ) as yoy_growth_pct
FROM orders
GROUP BY year, month
ORDER BY year, month;

-- Date range filtering
SELECT name, course, enroll_date
FROM students
WHERE enroll_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Recent data
SELECT name, course, marks
FROM students
WHERE enroll_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

-- Subquery in WHERE
SELECT name, course, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

-- Subquery in FROM
SELECT course, avg_marks
FROM (
 SELECT course, AVG(marks) as avg_marks
 FROM students
 GROUP BY course
) as course_stats
WHERE avg_marks > 80;

-- EXISTS — check if data exists
SELECT s.name, s.course
FROM students s
WHERE EXISTS (
 SELECT 1 FROM enrollments e 
 WHERE e.student_id = s.id AND e.status = 'active'
);

-- Correlated subquery
SELECT name, course, marks,
 marks - (SELECT AVG(marks) FROM students s2 WHERE s2.course = s1.course) as diff_from_avg
FROM students s1;