select
    -- Grouping dimension
    tier_1,

    -- Average time to conversion (First Touch only)
    avg(
        case 
            when first_touch = 1 
            then datediff(day, timestamp_events, timestamp_conv)
        end
    ) as first_touch_time_to_convert_days,

    -- Average time to conversion (Any Touch)
    avg(
        datediff(day, timestamp_events, timestamp_conv)
    ) as any_touch_time_to_convert_days

from <database>.<schema>.<log_level_mta_table>
where 
    date >= dateadd('day', -30, current_date)
group by 1
order by 1;

select
    tier_1,

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 0 and 7 
        then 1 else 0 
    end) as "0_7D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 8 and 14 
        then 1 else 0 
    end) as "8_14D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 15 and 30 
        then 1 else 0 
    end) as "15_30D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 31 and 60 
        then 1 else 0 
    end) as "31_60D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 61 and 90 
        then 1 else 0 
    end) as "61_90D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) > 90 
        then 1 else 0 
    end) as ">90D"

from <database>.<schema>.<log_level_mta_table>
where date >= dateadd('day', -30, current_date)
group by 1
order by 1;