9.SQL for Data Science & Predictive Analytics
1. SQL for Data Preparation
Data preparation is a critical step in any data science workflow. SQL helps clean, transform, and normalize data before feeding it into machine learning models.
Example: Handling Missing Values
SELECT customer_id, COALESCE(age, 30) AS age
FROM customers;
Example: Normalizing Values
SELECT customer_id,
(salary – MIN(salary) OVER()) / (MAX(salary) OVER() – MIN(salary) OVER()) AS normalized_salary
FROM customers;
Feature engineering involves creating new variables that help improve model performance.
Example: Creating Age Groups
SELECT customer_id,
CASE
WHEN age < 25 THEN ‘Youth’
WHEN age BETWEEN 25 AND 45 THEN ‘Adult’
ELSE ‘Senior’
END AS age_group
FROM customers;
Sample Data Table
| customer_id | age | age_group |
|---|---|---|
| 101 | 22 | Youth |
| 102 | 35 | Adult |
| 103 | 60 | Senior |
3. SQL for Predictive Analytics Integration
Example: Exporting Training Data
SELECT customer_id, age, salary, churn
FROM customers
WHERE signup_date < ‘2022-01-01’;
4. Practice Exercises
- Create a query to calculate rolling average of monthly sales.
- Write a query to classify customers into risk categories based on spending.
5. Real-World Project: Customer Churn Prediction
Use SQL to prepare a dataset for predicting customer churn. Include features like tenure, usage frequency, and support interactions.
Example Query
SELECT customer_id,
DATEDIFF(CURRENT_DATE, signup_date) AS tenure_days,
support_calls,
CASE WHEN last_purchase < CURRENT_DATE – INTERVAL 90 DAY THEN 1 ELSE 0 END AS likely_to_churn
FROM customers;
6. Visual Aid: ML Pipeline Diagram
7. FAQs
How does SQL help in data science?
SQL is used to extract, clean, and transform data, which is essential for building accurate machine learning models.
Can SQL perform predictive analytics?
SQL prepares data for predictive analytics but does not perform modeling itself. It integrates with tools like Python and R for model training.
What is feature engineering in SQL?
Feature engineering in SQL involves creating new columns or transforming existing ones to improve model performance.