Answers and explanations · Prepare AI features without future leakage

Exercise 11a

Six customers registered before 1 March. Gita registered on the cutoff and Hari after it, so both are excluded.

SELECT COUNT(*) AS eligible_customers FROM aabha_sql.customers
WHERE signup_date<DATE '2025-03-01';

Expected output:

Runnable answer: sql/solutions/11a.sql

Exercise 11b

Six rows and six distinct identifiers support one-row-per-customer grain. A real system can still leak future completion status or late-arriving corrections into past features. Past order dates alone do not prove past availability.

WITH features AS (
WITH eligible_orders AS (
 SELECT o.order_id,o.customer_id,o.order_date,
 SUM(i.quantity*i.unit_price-i.line_discount) AS sales_before_refunds
 FROM aabha_sql.orders AS o JOIN aabha_sql.order_items AS i ON i.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-03-01'
 GROUP BY o.order_id,o.customer_id,o.order_date
), customer_features AS (
 SELECT customer_id,COUNT(*) AS completed_orders,
 SUM(sales_before_refunds) AS sales_before_refunds,MAX(order_date) AS last_order_date
 FROM eligible_orders GROUP BY customer_id
)
SELECT c.customer_id,COALESCE(f.completed_orders,0) AS completed_orders,
 COALESCE(f.sales_before_refunds,0) AS sales_before_refunds,
 DATE '2025-03-01'-f.last_order_date AS recency_days
FROM aabha_sql.customers AS c LEFT JOIN customer_features AS f ON f.customer_id=c.customer_id
WHERE c.signup_date < DATE '2025-03-01'
ORDER BY c.customer_id
)
SELECT COUNT(*) AS rows,COUNT(DISTINCT customer_id) AS distinct_customers FROM features;

Expected output:

Runnable answer: sql/solutions/11b.sql