time difference between events in two different tables

I have a paid_activity and free_activity table:

paid_activity model:

free_activity model:

I need to produce a dormancy table with the model:

The dormancy table should have a 1:1 row correspondence to the paid_activity table.

I started by making an intermediate paid_dormancy table without the free_<X> fields:

Code:

select
    pk
    , date
    , uid
    , pid
    , date - lag(date) ignore nulls over(
        partition by uid
        order by date) paid_dormancy
    , date - lag(date) ignore nulls over(
        partition by uid, pid
        order by date) paid_product_dormancy
from paid_activity

I'm having trouble figuring out how to merge in free_activity to build the dormancy table.

I cobbled together the following query which doesn't produce the correct results at all:

with dormancy_union as (
    select
        ppk pk
        , date
        , uid
        , pid
        , paid_dormancy
        , paid_product_dormancy
        , iff(ppk is null, null,
            date - lag(date) ignore nulls over(
                partition by uid
                order by fpk, date
            )) free_dormancy
        , iff(ppk is null, null,
            date - lag(date) ignore nulls over(
                partition by uid, pid
                order by fpk, date
            )) free_product_dormancy
    from (
        select pk ppk, null fpk, * from paid_dormancy
        union all
        select null ppk, pk fpk, *, null, null from free_activity
)
select * 
from dormancy_union
where pk is not null
order by date;

So with some example data:

with paid_activity(pk, date, uid, pid) as (
    select 
        hash(column1, column2, column3), 
        to_date(column1, 'yyyy-mm-dd'), 
        column2, 
        column3
    from values
    ('2023-02-14', 1, 10),
    ('2023-02-01', 1, 11),
    ('2023-01-13', 1, 10)
), free_activity(pk, date, uid, pid) as (
    select 
        hash(column1, column2, column3), 
        to_date(column1, 'yyyy-mm-dd'), 
        column2, 
        column3
    from values
    ('2023-02-15', 1, 10),
    ('2023-02-11', 1, 10),
    ('2023-01-20', 1, 10),
    ('2023-01-01', 1, 10)
)

we can build the priors for the free products by do a date join, and then keep the "most recent" per input rows..

), with_priors as (
    select z.*
        ,fp.date as fp_date
    from (
        select p.*
            ,f.date as f_date
        from paid_activity as p
        left join free_activity as f 
            on f.date <= p.date 
            and p.uid = f.uid
        qualify row_number() over (partition by p.date, p.uid, p.pid order by f.date desc) = 1
    ) as z
    left join free_activity as fp 
        on fp.date <= z.date 
        and fp.uid = z.uid 
        and fp.pid = z.pid
    qualify row_number() over (partition by z.date, z.uid, z.pid order by fp.date desc) = 1
)
select 
    p.*
    ,lag(p.date)ignore nulls over(partition by p.uid order by p.date) as l_p_act
    ,lag(p.date)ignore nulls over(partition by p.uid, p.pid order by p.date) as l_pp_act
    ,datediff('days', lag(p.date)ignore nulls over(partition by p.uid order by p.date), p.date) as paid_dormancy
    ,datediff('days', lag(p.date)ignore nulls over(partition by p.uid, p.pid order by p.date), p.date) as paid_product_dormancy
    ,datediff('days', p.f_date, p.date) as free_dormancy
    ,datediff('days', p.fp_date, p.date) as free_product_dormancy
from with_priors as p
order by 2,3,4;

so that last block can be cleaner:

select 
    p.pk, p.date, p.uid, p.pid
    ,datediff('days', lag(p.date)ignore nulls over(partition by p.uid order by p.date), p.date) as paid_dormancy
    ,datediff('days', lag(p.date)ignore nulls over(partition by p.uid, p.pid order by p.date), p.date) as paid_product_dormancy
    ,datediff('days', p.f_date, p.date) as free_dormancy
    ,datediff('days', p.fp_date, p.date) as free_product_dormancy
from with_priors as p
order by 2,3,4;

giving: