Menu

SQL for Data Analysis

SQL is used throughout the data science lifecycle to retrieve historical observations, calculate business metrics, and prepare feature datasets for predictive modelling.

For example, a customer churn model may require each customer's order frequency, total spending, average order value, and most recent purchase date.

These features can be calculated directly in a relational database before the resulting dataset is loaded into Python.

SQL feature extraction involves transforming relational records into structured analytical variables that represent the information available at a specific observation point.

The process requires accurate aggregation, consistent observation levels, appropriate date filtering, and safeguards against data leakage.

What Is an Analytical Dataset?

An analytical dataset is a structured collection of observations and variables prepared for statistical analysis or machine learning.

In supervised machine learning, it commonly contains:

  • An identifier for each observation
  • Input features
  • A target variable
  • Relevant observation or prediction timestamps

For example:

customer_id total_orders total_spending average_order_value churn
C001 5 20000 4000 0
C002 2 3500 1750 1
C003 8 48000 6000 0

Each row represents one customer.

The target variable indicates whether the customer churned during the defined prediction period.

The dataset must maintain a consistent observation level.

Customer-level prediction requires one row per customer per prediction timestamp, rather than one row per individual transaction.

How Do You Calculate Customer-Level Features in SQL?

Consider an orders table containing:

  • order_id
  • customer_id
  • order_date
  • amount
  • status

A customer-level summary can be calculated using aggregation.

SELECT
    customer_id,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_spending,
    AVG(amount) AS average_order_value
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id;

The query generates three features for every customer with at least one completed order.

However, historical prediction datasets require an observation cutoff date.

Otherwise, transactions occurring after the intended prediction point may leak future information.

What Is Time-Based Feature Extraction?

Time-based features summarise observations from a specified historical period.

For example, a churn prediction model may use orders completed during the previous 30 days.

Suppose the prediction timestamp is November 1, 2026.

A query can restrict the historical window.

SELECT
    customer_id,
    COUNT(*) AS orders_30d,
    SUM(amount) AS spending_30d
FROM orders
WHERE status = 'Completed'
  AND order_date >= TIMESTAMP '2026-10-02 00:00:00'
  AND order_date < TIMESTAMP '2026-11-01 00:00:00'
GROUP BY customer_id;

The query uses a half-open time interval.

It includes records starting October 2 and excludes records on or after November 1.

For a timestamp stored in a particular timezone, the cutoff must be interpreted consistently.

What Is RFM Analysis in SQL?

RFM analysis summarises customer purchasing behaviour through three measurements.

Recency: Time since the customer's most recent purchase.

Frequency: Number of purchases within the observation period.

Monetary Value: Total or average transaction value.

These features are commonly used in customer segmentation and retention analysis.

Calculating RFM Features

SELECT
    customer_id,
    DATE '2026-11-01'
        - MAX(order_date::date) AS recency_days,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary_value
FROM orders
WHERE status = 'Completed'
  AND order_date < TIMESTAMP '2026-11-01'
GROUP BY customer_id;

The example calculates recency relative to November 1, 2026.

Frequency and monetary value cover all available qualifying historical records before that date.

A fixed lookback window can be added when the analysis requires activity from a specific period.

Why Are RFM Features Useful?

RFM features capture different aspects of customer behaviour.

A customer with low recency and high frequency has purchased recently and often.

A customer with high recency may have been inactive for an extended period.

However, RFM segments are descriptive patterns rather than proof that customers will behave in a particular way.

How Do You Create Conditional Features Using CASE WHEN?

The CASE expression applies conditional logic within SQL queries.

For example, customers can be classified into spending categories.

SELECT
    customer_id,
    SUM(amount) AS total_spending,
    CASE
        WHEN SUM(amount) >= 20000 THEN 'High Value'
        WHEN SUM(amount) >= 5000 THEN 'Regular'
        ELSE 'Occasional'
    END AS customer_segment
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id;

The query assigns segments using predefined spending thresholds.

These thresholds are illustrative and should be defined according to the business objective.

SQL CASE expressions are also useful for creating binary indicators.

For example:

CASE
    WHEN payment_failures > 0 THEN 1
    ELSE 0
END AS has_payment_failure

This generates a numerical indicator showing whether a customer has recorded a payment failure.

How Do You Combine Multiple Feature Sources?

A machine learning dataset may require information from several tables.

Consider three tables:

Customers: Customer ID, signup date, subscription plan

Orders: Customer ID, transaction amount, order date

Support Tickets: Customer ID, ticket date, issue type

The goal is to create one row per customer.

A safe approach aggregates transaction and support data separately before joining.

WITH order_features AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spending
    FROM orders
    WHERE status = 'Completed'
    GROUP BY customer_id
),
support_features AS (
    SELECT
        customer_id,
        COUNT(*) AS support_ticket_count
    FROM support_tickets
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.subscription_plan,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.total_spending, 0) AS total_spending,
    COALESCE(s.support_ticket_count, 0)
        AS support_ticket_count
FROM customers AS c
LEFT JOIN order_features AS o
    ON c.customer_id = o.customer_id
LEFT JOIN support_features AS s
    ON c.customer_id = s.customer_id;

This prevents an unintended many-to-many join between individual orders and support tickets.

For a historical predictive model, the feature CTEs must also include the required observation-time filters.

What Is Data Leakage in SQL Feature Extraction?

Data leakage occurs when information unavailable at prediction time influences model development.

Consider a model predicting whether a customer will cancel a subscription during the next 30 days.

The model may use:

  • Previous purchase frequency
  • Historical payment failures
  • Past support interactions
  • Recent usage changes

However, it cannot use the final cancellation status as an input feature because that information reveals the future outcome.

Similarly, customer spending calculated using transactions after the observation date would introduce future information.

Correct Feature Extraction Principle

Historical Records Before Prediction Time → Feature Calculation → Prediction → Future Target Window

The time boundary should be applied consistently across all source tables.

How Do You Detect Data Quality Issues Using SQL?

SQL can identify missing values, duplicates, and invalid records before Python processing.

Counting Missing Values

SELECT
    COUNT(*) AS total_records,
    COUNT(*) FILTER (
        WHERE customer_id IS NULL
    ) AS missing_customer_ids,
    COUNT(*) FILTER (
        WHERE amount IS NULL
    ) AS missing_amounts
FROM orders;

The FILTER syntax shown is supported by PostgreSQL.

Identifying Duplicate Transaction IDs

SELECT
    order_id,
    COUNT(*) AS record_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

The query identifies order IDs appearing more than once.

If order_id is intended to be unique, these records require investigation.

Identifying Potentially Invalid Amounts

SELECT *
FROM orders
WHERE amount < 0;

Negative amounts may represent incorrect entries or legitimate refunds, depending on the data model.

Therefore, validation rules must reflect the meaning of each column.

SQL can aggregate transactions into daily or monthly periods.

For example, monthly completed-order revenue can be calculated using PostgreSQL's DATE_TRUNC() function.

SELECT
    DATE_TRUNC('month', order_date)
        AS sales_month,
    SUM(amount) AS monthly_revenue
FROM orders
WHERE status = 'Completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY sales_month;

This produces one row per month.

The resulting dataset can support trend analysis and forecasting.

For forecasting, it is important to include periods with zero sales when they represent genuine observations. Grouping only existing transactions may omit such periods.

How Do You Load SQL Data Into Pandas?

Once the database query produces the required dataset, it can be loaded into Python.

import pandas as pdfrom sqlalchemy import create_engineengine = create_engine(    "postgresql+psycopg://user:password@localhost:5432/retail")query = """SELECT    customer_id,    COUNT(*) AS order_count,    SUM(amount) AS total_spendingFROM ordersWHERE status = 'Completed'GROUP BY customer_id"""df = pd.read_sql(query, engine)print(df.head())

The connection string is illustrative and requires valid database credentials, a reachable PostgreSQL server, and the appropriate Python driver.

In production applications, credentials should be managed securely rather than embedded directly in source code.

Why Combine SQL and Pandas?

SQL performs filtering, joining, and aggregation close to the stored data.

Pandas supports further manipulation, statistical summaries, and integration with Python machine learning libraries.

Using SQL to reduce large transactional datasets into relevant analytical tables can also decrease data transfer and Python memory requirements.

How Does SQL Support Exploratory Data Analysis?

SQL supports many exploratory operations before data is exported.

EDA Task SQL Operation
Count observations COUNT(*)
Calculate mean AVG()
Find minimum and maximum MIN(), MAX()
Count categories GROUP BY
Filter invalid records WHERE
Examine missing values IS NULL
Identify duplicates GROUP BY, HAVING
Calculate cumulative metrics Window functions
Compare historical records LAG(), LEAD()

Some databases also support percentile functions, standard deviations, and advanced statistical calculations.

The exact syntax varies across database systems.

What Are Best Practices for SQL in Data Science?

Select Relevant Columns: Avoid transferring unnecessary data.

Define the Observation Level: Establish whether each row represents a transaction, customer, product, or time period.

Use Consistent Time Boundaries: Prevent future records from influencing historical predictions.

Validate Join Relationships: Avoid unintended duplication from one-to-many and many-to-many joins.

Handle NULL Values Carefully: Distinguish missing information from legitimate zero values.

Use CTEs for Complex Queries: Organise multi-stage transformations clearly.

Check Query Performance: Examine execution plans and relevant indexes when queries become expensive.

Preserve Reproducibility: Maintain versioned query logic and consistent data definitions.

Common SQL Mistakes in Data Science

  1. Calculating features using records from after the prediction date.
  2. Joining transactional tables without checking row multiplication.
  3. Treating missing values as zero without business justification.
  4. Combining multiple observation levels in one analytical dataset.
  5. Ignoring timestamp and timezone differences.
  6. Exporting complete tables when SQL aggregation would be sufficient.

Data Science Tutorial for Beginners

VA

Vaishali Ardhana



Learn via Video Course

undefined Logo

0 hrs



Get in Touch For Details