hard20 pts · first answer countsscdsqldata-modeling
Double counting in an SCD2 join
dim_customer has two versions for customer 7: Dubai valid 2026-01-01 to 2026-05-01, and Abu Dhabi valid 2026-05-01 to 9999-12-31. Facts: an order on 2026-03-10 (50) and one on 2026-05-01 (100). What SUM(amount) does this query return?
SELECT SUM(f.amount) FROM fact_orders f JOIN dim_customer d ON f.customer_id = d.customer_id AND f.order_date BETWEEN d.valid_from AND d.valid_to;