SQL Window Functions

The Core Tool of Offline Feature Engineering

In machine learning feature engineering, raw database event logs must be converted into temporal rolling features:

• "What was this user's total spending in the 30 days prior to this transaction?"
• "What was the price of the previous item viewed by this user?"
• "Rank user clicks chronologically within each active session."

Standard GROUP BY queries fail because they collapse multiple rows into a single summary row.

SQL Window Functions compute aggregate values across related rows while retaining every individual row identity!

Standard GROUP BY:   100 User Click Rows  ──► Collapses to 1 Summary Row
SQL Window Function: 100 User Click Rows  ──► Retains 100 Individual Rows + Adds Computed Feature Columns!

Anatomy of a Window Function

SELECT
  user_id,
  transaction_time,
  amount,
  AVG(amount) OVER (
    PARTITION BY user_id
    ORDER BY transaction_time
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg_amount
FROM user_transactions;

┌──────────────────────────┬──────────────────────────┬──────────────────────────┐
│ PARTITION BY             │ ORDER BY                 │ FRAME SPECIFICATION      │
├──────────────────────────┼──────────────────────────┼──────────────────────────┤
│ Divides rows into        │ Orders rows chronologically│ Defines exact rolling    │
│ isolated calculation     │ within each partition    │ window bounds (for       │
│ groups (per user_id).    │ window.                  │ example 6 preceding rows)│
└──────────────────────────┴──────────────────────────┴──────────────────────────┘

Essential Window Functions for ML Features

1. Temporal Transition Features (LAG and LEAD)

Access values from previous or future rows relative to current row:

-- Feature: Time elapsed since user's previous action
SELECT
  user_id,
  event_time,
  LAG(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time
FROM user_events;

2. Chronological Ranking Features (ROW_NUMBER and DENSE_RANK)

Rank events within a user session:

-- Feature: Order index of click within current session
SELECT
  session_id,
  click_time,
  ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY click_time) AS click_rank
FROM session_clicks;

3. Rolling Cumulative Aggregations (SUM, AVG, COUNT)

Compute cumulative sums or rolling window averages:

-- Feature: Cumulative spend by user over time
SELECT
  user_id,
  purchase_time,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY purchase_time) AS cumulative_spend
FROM purchases;

Say this out loud

SQL Window Functions calculate rolling aggregations, ranks, and lead lag temporal features without collapsing row identities. Using the OVER clause with PARTITION BY and ORDER BY, window functions compute rolling averages, cumulative sums, and time deltas between events, forming the backbone of offline feature engineering for machine learning pipelines.

Followups to expect

1. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()? ROW_NUMBER assigns unique sequential integers. RANK leaves gaps after ties (1, 2, 2, 4). DENSE_RANK leaves no gaps after ties (1, 2, 2, 3).
2. Why be careful with RANGE BETWEEN vs ROWS BETWEEN in temporal window frames? ROWS BETWEEN counts physical rows, whereas RANGE BETWEEN evaluates numeric value ranges, which can cause unexpected performance slowdowns on duplicate order timestamps.