Write a single ANSI‑SQL query that produces, for every customer who has at least one non‑canceled order ever: (1) last_order_ts across all time considering only status IN ('COMPLETED','SHIPPED'); (2) distinct_products_last_7d = count of distinct product_id purchased in the last 7 days; (3) net_spend_last_7d = sum(qty*unit_price) for orders with status IN ('COMPLETED','SHIPPED') in the last 7 days minus refunds recorded in the last 7 days. If a customer has no qualifying activity in the last 7 days, return 0 and 0.00 for the metrics but still report their last_order_ts if any. Consider 'today' = 2025-09-01 23:59:59 and the rolling 7‑day window as [2025-08-26 00:00:00, 2025-09-01 23:59:59]. Ties for last_order_ts are resolved by the max timestamp. Schema and small ASCII sample tables: customers +-------------+-------+ | customer_id | name | +-------------+-------+ | 1 | Alice | | 2 | Bob | | 3 | Chen | orders +----------+-------------+---------------------+-----------+ | order_id | customer_id | order_ts | status | +----------+-------------+---------------------+-----------+ | 1001 | 1 | 2025-08-25 22:00:00 | COMPLETED | | 1002 | 1 | 2025-08-26 09:00:00 | SHIPPED | | 1003 | 2 | 2025-08-28 12:30:00 | CANCELED | | 1004 | 2 | 2025-08-31 15:45:00 | COMPLETED | | 1005 | 3 | 2025-09-01 20:10:00 | COMPLETED | order_items +----------+------------+-----+------------+ | order_id | product_id | qty | unit_price | +----------+------------+-----+------------+ | 1001 | p10 | 1 | 20.00 | | 1002 | p10 | 2 | 19.00 | | 1002 | p11 | 1 | 5.00 | | 1003 | p11 | 3 | 5.50 | | 1004 | p12 | 1 | 30.00 | | 1005 | p10 | 1 | 18.50 | | 1005 | p13 | 2 | 7.00 | returns +----------+------------+-----+---------------+---------------------+ | order_id | product_id | qty | refund_amount | return_ts | +----------+------------+-----+---------------+---------------------+ | 1002 | p10 | 1 | 19.00 | 2025-08-30 10:00:00 | | 1004 | p12 | 1 | 30.00 | 2025-09-02 10:00:00 | | 1001 | p10 | 1 | 20.00 | 2025-08-26 08:00:00 | Requirements and clarifications: - Only orders with status IN ('COMPLETED','SHIPPED') contribute to last_order_ts and spending; CANCELED orders contribute to neither. - distinct_products_last_7d counts unique product_id across qualifying orders in the 7‑day window. - net_spend_last_7d subtracts refunds with return_ts in the 7‑day window (even if the original order was earlier). If no orders ever, exclude the customer from the output; if orders exist but none in the last 7 days, metrics are 0 and 0.00. - Write one query; use common table expressions if desired; avoid vendor‑specific extensions beyond ANSI SQL/window functions.