How to Use SQL for Feature Engineering in Machine Learning

such as one row per customer, with numeric columns that describe their behavior. Those columns are called features, and building them from raw data is feature engineering.

SQL is well suited to this work, because most useful features are aggregates: how many, how much, how recently.

Common Feature Ideas

Building a Feature Table

Take a transactions table with user_id, txn_date and amount. We want one row per user, as of 1 October 2026.

SELECT user_id,
       COUNT(*)                                   AS txn_count,
       SUM(amount)                                AS total_spend,
       ROUND(AVG(amount), 2)                      AS avg_spend,
       DATE '2026-10-01' - MAX(txn_date)          AS days_since_last,
       CASE WHEN SUM(amount) >= 1000 THEN 1 ELSE 0 END AS big_spender
FROM transactions
WHERE txn_date < DATE '2026-10-01'
GROUP BY user_id
ORDER BY user_id;

Output:

Code Explanation:

• GROUP BY user_id collapses each user’s transactions into a single row.
• COUNT, SUM and AVG describe how often and how much each user spends.
• Subtracting the user’s latest date from the reference date gives the number of days of inactivity.
• CASE WHEN converts the total into a 0 or 1 flag, which models handle easily.

Two Traps to Avoid

The most serious one is data leakage. The WHERE txn_date < DATE '2026-10-01' line is there on purpose. If you predict something as of 1 October, features must use only data from before that date. Using later data makes the model look brilliant in testing and fail in real use.

The other trap is missing history. Users with no transactions simply won’t appear in a plain GROUP BY. Start from the users table and use a LEFT JOIN, then fill the gaps with COALESCE, so every user gets a row with zeros instead of disappearing.