Skip to content
RED SAGSkills
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;
Choose one answer