SQL for Data Analysis and Data Science: Complete Practical Guide

1. SQL for Data Analysis

SQL is the primary tool for data analysts and business intelligence engineers. While application developers write queries to support individual user actions (such as logging in or checking out an item), data analysts write analytical SQL to evaluate aggregate business performance over time.

Analytical SQL powers executive reporting dashboards and extracts high-level Key Performance Indicators (KPIs):

Customer Lifetime Value (LTV): Total historical net revenue generated per active account.
Average Order Value (AOV): Mean transaction spend per completed checkout.
Churn Rate: Percentage of subscribers who cancel their plans within a defined billing cycle.

2. SQL for Data Science

Data scientists spend up to 80% of their project time sourcing, cleaning, and shaping raw data before training machine learning models.

Executing data preparation inside an enterprise data warehouse using SQL is significantly faster than pulling terabytes of raw unaggregated rows into a local Python environment. SQL handles filtering, multi-table joins, and feature aggregations directly on clustered database hardware, allowing data scientists to export compact, ready-to-train feature matrices into Python or R.

`In-Database Feature Engineering`

Heavy Lifting in SQL:Perform massive aggregations (e.g., total logins per user in past 90 days) directly in database engines.
Clean Data in Python:Load only the resulting feature table into pandas to train scikit-learn or PyTorch models.

3. Exploring a New Dataset (Exploratory Data Analysis)

When encountering an unfamiliar dataset, begin with structured exploratory queries to evaluate record volume, cardinality, and boundary dates:

Initial Exploratory Data Analysis

SQL

Measure total records, distinct buyers, date ranges, and status distributions

4. Cleaning Data with SQL

Real-world data is frequently messy, inconsistent, and improperly formatted. Common data cleansing patterns include:

Trimming Whitespace: TRIM(user_input) strips accidental leading and trailing spaces.
Standardizing Case: LOWER(email) guarantees that 'User@Example.com' matches 'user@example.com'.
Sanitizing Characters: REPLACE(phone, '-', '') strips formatting symbols to store standardized numeric strings.

5. Handling NULL and Missing Values

Missing data can bias statistical averages and break calculations. SQL provides specialized functions to manage nulls safely:

COALESCE(col, fallback): Returns the first non-null expression from a list of arguments.
NULLIF(col, ''): Converts blank strings or placeholder values into true SQL nulls so aggregate functions handle them correctly.

Imputing and Handling NULL Values

SQL

Replace missing relational identifiers with safe fallback placeholders

6. Removing Duplicate Rows

Duplicate records skew reporting metrics and double-count revenue. The standard SQL pattern detects duplicates using GROUP BY and filters them using ROW_NUMBER() inside a Common Table Expression:

-- De-duplicating records keeping only the most recent entry
WITH RankedRecords AS (
    SELECT id, email, created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at DESC
        ) AS rn
    FROM user_signups
)
DELETE FROM user_signups
WHERE id IN (SELECT id FROM RankedRecords WHERE rn > 1);

7. Percentages and Ratios

Calculating each category's share of the overall total requires pairing group aggregations with window functions:

Calculating Category Revenue Ratios

SQL

Pair group aggregations with window totals to compute contribution shares

8. Year-over-Year and Month-over-Month Growth

Tracking period-over-period growth measures business momentum. Using LAG(), analysts access metrics from the preceding month or year to compute absolute deltas and percentage changes:

Month-over-Month Order Trajectory

SQL

Inspect order volumes against preceding periods using LAG

9. Cohort Analysis

A cohort is a group of subjects who share a defining demographic or temporal milestone (such as all customers whose first purchase occurred in January 2026).

Cohort analysis tracks how that specific cohort behaves over subsequent weeks or months, identifying whether customer retention and spending expand or decline over time.

10. Retention Analysis

Customer retention measures the percentage of users who return to make subsequent purchases after their initial sign-up period:

-- Retention analysis pattern
WITH FirstOrders AS (
    SELECT customer_id, MIN(order_date) AS first_order_date
    FROM orders GROUP BY customer_id
)
SELECT
    DATE_TRUNC('month', fo.first_order_date) AS cohort_month,
    COUNT(DISTINCT fo.customer_id) AS total_cohort_size,
    COUNT(DISTINCT o.customer_id) AS retained_customers
FROM FirstOrders fo
LEFT JOIN orders o ON o.customer_id = fo.customer_id
    AND o.order_date > fo.first_order_date
GROUP BY DATE_TRUNC('month', fo.first_order_date);

11. Funnel Analysis

Funnel analysis evaluates user drop-off across a sequential series of conversion steps (such as Homepage Visit -> Product View -> Add to Cart -> Checkout Completed).

By identifying the step with the sharpest percentage drop-off, product managers discover usability hurdles and checkout friction.

12. SQL vs Excel

Both tools are widely utilized in quantitative analytics, but address different data scales:

Feature	SQL	Microsoft Excel
Maximum Capacity	Billions of rows and petabytes of data	Strict limit of 1,048,576 rows per sheet
Reproducibility	100% reproducible through versioned code scripts	Prone to manual copy-paste errors
Hardware Utilization	Leverages remote multi-core database servers	Limited to local workstation memory and CPU
Automation	Scheduled automatically in scheduled pipelines	Requires manual spreadsheet refreshes
Primary Strength	Data extraction, transformation, filtering	Quick visual exploration and ad-hoc charts

13. SQL with Python (sqlite3, pandas, SQLAlchemy)

Python connects directly to relational engines, allowing developers to execute SQL queries and immediately load the outputs into pandas DataFrames for statistical visualization:

# Python data analysis workflow
import sqlite3
import pandas as pd

# Connect to database file
conn = sqlite3.connect('analytics.db')

# Query directly into pandas DataFrame
query = """
SELECT customer_id, COUNT(id) AS order_count, SUM(price * quantity) AS total_spend
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status != 'cancelled'
GROUP BY customer_id
"""
df = pd.read_sql_query(query, conn)

# Inspect first 5 rows in Python
print(df.head())

`Enterprise Connection with SQLAlchemy`

Connection Pooling:Use create_engine('postgresql://user:pass@host/db') from SQLAlchemy to handle connection pools.
Fast Loading:Pair pd.read_sql() with SQLAlchemy engines to process multi-million row datasets efficiently.

Knowledge Check: SQL for Data Analysis

1. What is the primary purpose of Exploratory Data Analysis?

2. How do you identify duplicate business keys in SQL?

3. Which function enables period-over-period growth comparisons?

4. What does a cohort analysis track over time?

5. What is a major advantage of SQL over Excel?

6. Which pandas function loads SQL queries into DataFrames?