The subscriptions table contains the current product_id.
| subscription_id | product_id | created_at | example_case |
|---|---|---|---|
| 1 | 900 | 2018-01-01 | subscription that hasn't changed |
| 2 | 907 | 2018-01-01 | subscription that has changed once |
| 3 | 956 | 2018-01-01 | subscription that has changed many times |
The subscription_change_log shows when changes to this product_id occurred:
| subscription_id | product_id | new_product_id | happened_at |
|---|---|---|---|
| 2 | 906 | 907 | 2018-02-01 |
| 3 | 948 | 904 | 2018-02-01 |
| 3 | 904 | 962 | 2018-03-01 |
| 3 | 962 | 956 | 2018-04-01 |
I want to create one record every time a subscription changes product id, like so:
| subscription_id | product_id | valid_from | valid_to |
|---|---|---|---|
| 1 | 900 | 2018-01-01 | |
| 2 | 906 | 2018-01-01 | 2018-02-01 |
| 2 | 907 | 2018-02-01 | |
| 3 | 948 | 2018-01-01 | 2018-02-01 |
| 3 | 904 | 2018-02-01 | 2018-03-01 |
| 3 | 962 | 2018-03-01 | 2018-04-01 |
| 3 | 956 | 2018-04-01 |
with subscriptions as (
select * from {{ ref('subscriptions') }}
),
subscription_change_log as (
select
*,
-- in dbt, do this in an upstream model
row_number() over (
partition by subscription_id
order by happened_at
) = 1 as is_first_change
from {{ ref('subscription_change_log') }}
),
subscription_created as (
select
subscriptions.subscription_id,
subscriptions.created_at as happened_at,
-- use the OG product_id if it has changed
coalesce(
first_change.old_product_id,
subscriptions.product_id
) as product_id
from subscriptions
left join subscription_change_log as first_change
on subscriptions.subscription_id = first_change.subscription_id
and first_change.is_first_change is true
),
changes as (
select
subscription_id,
happened_at,
new_product_id as product_id
from subscription_change_log
),
unioned as (
select * from subscription_created
union all
select * from changes
)
select * from unioned