Revenue by region when the store dimension has duplicate keys

store_sales_fact (sale_id, store_id, sold_on, amount) records sales. dim_store (store_id, store_name, region, is_current, updated_at) describes the stores — but when a store moved to another region, the load added a new row and only flipped is_current on the old one, so some store_ids now appear twice. Exactly one row per store has is_current = TRUE.

Report revenue by region, attributing every sale to its store's current region. Regions with no revenue under that rule don't appear.

Columns: region, revenue. Sort by region.

Tables & sample data

store_sales_fact

8 rows total · first 5 shown

sale_idbigint	store_idvarchar	sold_ondate	amountdouble
1	s1	2024-04-01	120
2	s2	2024-04-01	80
3	s2	2024-04-02	45
4	s3	2024-04-02	200
5	s4	2024-04-03	60

dim_store

8 rows total · first 5 shown

store_idvarchar	store_namevarchar	regionvarchar	is_currentboolean	updated_atdate
s1	Harbour St	North	true	2023-01-10
s2	Mill Lane	North	false	2023-01-10
s2	Mill Lane	South	true	2024-02-01
s3	Old Quay	East	false	2023-01-10
s3	Old Quay	West	true	2024-03-15

query.sql

Loading editor…