Question

A data engineer is building a churn-prediction model in BigQuery ML from a table of daily customer transactions. The team wants each training row to include a feature representing the number of days since the customer's previous purchase, computed directly in SQL so the same logic is reproducible. Which approach correctly generates this feature during data preparation?

Accepted Answer

Use the DATE_DIFF function combined with the LAG window function partitioned by customer_id and ordered by purchase_date.

Answer

Use a self-join of the transactions table on customer_id without any ordering, then subtract the two dates.

Answer

Use the RAND function to sample a prior purchase date for each row and compute the difference.

Answer

Use APPROX_COUNT_DISTINCT on purchase_date grouped by customer_id to estimate purchase frequency.