[Feature] New model - Order lines with correct subscription from subscription_history

[fivetran-catfritz]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

This is a continuation of issue #9

There is a need for a model that matches the correct subscription record from subscription_history to an order and order_line. The current subscription overview deals with most recent subscription records, but this does not give insight into how a subscription might change over time. For example, a customer might change the product several times over the life of a subscription.

During exploration the customer provided an example model, and I came up with the following model translated to our style and a few other adjustments, but it will need validation and more thorough vetting. (see the linked issue for more history and models and details)

Describe alternatives you've considered

with orders as (
    select *
    from {{ var('order') }} 

), order_line_item as (
    select *
    from {{ var('order_line_item') }} 

), orders_with_line_items as (
    select
        orders.*,
        {{ dbt_utils.star(from=ref('stg_recharge__order_line_item'), except=['order_id'], relation_alias='order_line_item') }}

    from orders
    left join order_line_item 
        on order_line_item.order_id = orders.order_id

), subscription_history as (
    select 
        *,
        lead(subscription_updated_at) over (partition by subscription_id 
            order by subscription_updated_at asc)
            as next_subscription_update

    from {{ var('subscription_history') }}

), add_subscription_history_details as (
    select
        orders_with_line_items.*,
        subscription_history.subscription_updated_at,
        subscription_history.order_interval_frequency,
        case
            when lower(purchase_item_type) = 'onetime' then 1
            else row_number() over (partition by order_id, purchase_item_id order by subscription_updated_at desc)
            end as closest_record_to_order_date 

        from orders_with_line_items
        left join subscription_history
            on subscription_history.subscription_id = orders_with_line_items.purchase_item_id 
            and date(order_processed_at) >= subscription_history.subscription_updated_at
            and date(order_processed_at) <= coalesce(subscription_history.next_subscription_update, order_processed_at)
)

select * 
from add_subscription_history_details

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response

[dejanzalik]

hey @fivetran-catfritz, thanks for your messages! One note from me regarding using the timestamp is that (as we discussed during the call) we'd need to know if the timestamps (in terms of processing) are accurate enough for placing between the update timestamps. I guess we'll have to wait for Recharge to confirm this. Other than that, all good for me!