179 questions
No questions match those filters.
How do you use SQL to build a feature matrix for an ML...
This is one of the questions in the full AI/ML interview bank. Pro unlocks all 1789 questions; Premium includes the same bank plus the highest daily Practice limit.
See plansThe pattern that scales best is one CTE per feature family, joined together at the end on the entity key, rather than one enormous query trying to compute everything in a single pass. A user_purchase_features CTE handles order-count, total-revenue, average-order-value, and tenure in one GROUP BY over the orders table; a separate user_behavior_features CTE handles page-view counts, cart-add counts, and cart-to-purchase ratio from a different event table; the final SELECT joins the two on user_id. Keeping them separate means each CTE can be tested and validated independently before trusting the combined matrix.
Ratio features deserve special care because they’re the most common source of a silent production bug: cart_to_purchase_ratio = purchases / cart_adds divides by zero for any user who added items to a cart but never purchased and never had a corresponding row, unless it’s wrapped as purchases * 1.0 / NULLIF(cart_adds, 0) — NULLIF returns NULL instead of raising a divide-by-zero error, and most ML pipelines handle a NULL feature far more gracefully than a query that fails outright on a specific row.
The label itself frequently reuses exactly the same primitives as the features, which is worth calling out because it’s easy to accidentally introduce leakage here: a churn label computed as CASE WHEN days_since_last_order > 90 THEN 1 ELSE 0 END is safe if days_since_last_order is computed relative to a fixed cutoff date, but computing it relative to CURRENT_DATE in a query that also generates features from the full historical range risks the label window overlapping the feature window. Being explicit about a single fixed “as-of” date that both features and label are computed relative to is the discipline that prevents that class of bug.