avia_main_metrics_by_markets_platforms_day

mail@pastecode.io avatarunknown
sql
2 months ago
46 kB
3
Indexable
Never
create schema if not exists {{ params.schema }};


create table if not exists {{ params.schema }}.{{ params.relation }} (
    platform_type string,
    platform string,
    brand string,
    market string,
    source string,
    searches int,
    clicks int,
    bookings int,
    profit decimal(38, 2),
    profit_usd decimal(38, 2),
    price decimal(38, 2),
    price_usd decimal(38, 2),
    passengers int,
    cpc_profit decimal(38, 2),
    cpc_profit_usd decimal(38, 2),
    cpc_clicks int,
    sessions int,
    sessions_with_click int,
    sessions_with_search int,
    installs int,
    searches_domestic int,
    searches_international int,
    clicks_domestic int,
    clicks_international int,
    bookings_domestic int,
    bookings_international int,
    profit_domestic decimal(38, 2),
    profit_international decimal(38, 2),
    price_domestic decimal(38, 2),
    price_international decimal(38, 2),
    profit_usd_domestic decimal(38, 2),
    profit_usd_international decimal(38, 2),
    price_usd_domestic decimal(38, 2),
    price_usd_international decimal(38, 2),
    passengers_domestic int,
    passengers_international int,
    cpc_profit_domestic decimal(38, 2),
    cpc_profit_international decimal(38, 2),
    cpc_profit_usd_domestic decimal(38, 2),
    cpc_profit_usd_international decimal(38, 2),
    cpc_clicks_domestic int,
    cpc_clicks_international int
)
partitioned by (pdate string)
stored as parquet;

alter table {{ params.schema }}.{{ params.relation }}
add column if not exists total_ses_length_min decimal(38, 2);

alter table {{ params.schema }}.{{ params.relation }}
add column if not exists searches_round_trip decimal(38, 2);

alter table {{ params.schema }}.{{ params.relation }}
add column if not exists searches_with_children decimal(38, 2);

alter table {{ params.schema }}.{{ params.relation }}
add column if not exists searches_with_auth decimal(38, 2);


with
searches_last_market as (
    select *
    from
        (
            select
                market,
                coalesce(mobile_token, auid) as user_id,
                row_number() over (
                    partition by coalesce(mobile_token, auid)
                    order by created_at desc
                ) = 1 as latest
            from avia.searches
            where
                pdate
                between '{{ macros.ds_add(ds, params.partition_lookback) }}'
                and '{{ ds }}'
                and coalesce(mobile_token, auid) is not null
                and coalesce(mobile_token, auid) != ''
        ) as t
    where latest
),

sess_web_last_market as (
    select *
    from
        (
            select
                user_id,
                market,
                row_number() over (
                    partition by user_id order by start_ts desc
                ) = 1 as latest
            from analytics.sessions_web
            where
                pdate between
                '{{ macros.ds_add(ds, params.partition_lookback) }}'
                and '{{ ds }}'
        ) as t
    where latest
),

sess_mobile_last_market as (
    select *
    from
        (
            select
                user_id,
                lower(ip_country_iata) as market,
                row_number() over (
                    partition by user_id order by start_dts desc
                ) = 1 as latest
            from analytics.sessions_mobile
            where
                pdate between
                '{{ macros.ds_add(ds, params.partition_lookback) }}'
                and '{{ ds }}'
        ) as t
    where latest
),

sess_web as (
    select
        pdate,
        user_id,
        decode(
            platform,
            'android_web',
            'mobile_web',
            'ios_web',
            'mobile_web',
            'desktop',
            'web',
            'unknown',
            'other',
            platform
        ) as platform_type,
        case
            when user_agent
                iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
            then 'desktop'
            when user_agent iregexp '(ipad|iphone)'
            then 'ios_web'
            when user_agent iregexp 'android'
            then 'android_web'
            else 'other'
        end as platform,
        case
            when brand = 'WA' or page_url iregexp 'wayaway'
            then 'wayaway'
            when page_url iregexp 'aviasales'
            then 'aviasales'
            when page_url iregexp 'jetradar'
            then 'jetradar'
            else 'other'
        end as brand,
        case
            when
                source in (
                    'affiliate',
                    'ppc_stoyan',
                    'direct',
                    'organic',
                    'ppc_nm',
                    'ppc_no_marker'
                )
            then source
            else 'other'
        end as source,
        searches > 0 is_session_with_search,
        clicks > 0 is_session_with_click,
        start_ts,
        end_ts,
        ses_length_sec / 60 ses_length_min
    from analytics.sessions_web
    where
        pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
        and '{{ ds }}'
),

sess_mobile as (
    select
        pdate,
        user_id,
        'mobile' as platform_type,
        case
            when app_id iregexp 'ios'
            then 'ios'
            when app_id iregexp 'android'
            then 'android'
            else 'other'
        end as platform,
        case
            when brand = 'WA'
            then 'wayaway'
            when app_id iregexp 'aviasales'
            then 'aviasales'
            when app_id iregexp 'jetradar'
            then 'jetradar'
            else 'other'
        end as brand,
        clicks > 0 is_session_with_click,
        searches > 0 is_session_with_search,
        ses_length_sec / 60 ses_length_min
    from analytics.sessions_mobile
    where
        pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
        and '{{ ds }}'
),

sess_agg as (
    select
        pdate,
        sess_web.platform_type,
        sess_web.platform,
        sess_web.brand,
        coalesce(
            searches_last_market.market, sess_web_last_market.market, 'N/D'
        ) as market,
        source,
        count(*) as sessions,
        sum(sess_web.is_session_with_click) as sessions_with_clicks,
        sum(sess_web.is_session_with_search) as sessions_with_searches,
        sum(ses_length_min) as total_ses_length_min
    from sess_web
    left outer join
        searches_last_market on sess_web.user_id = searches_last_market.user_id
    left outer join
        sess_web_last_market on sess_web.user_id = sess_web_last_market.user_id
    group by
        pdate,
        platform_type,
        platform,
        brand,
        market,
        source

    union all

    select
        pdate,
        sess_mobile.platform_type,
        sess_mobile.platform,
        sess_mobile.brand,
        coalesce(
            searches_last_market.market, sess_mobile_last_market.market, 'N/D'
        ) as market,
        'mobile' as source,
        count(*) as sessions,
        sum(sess_mobile.is_session_with_click) as sessions_with_clicks,
        sum(sess_mobile.is_session_with_search) as sessions_with_searches,
        sum(ses_length_min) as total_ses_length_min
    from sess_mobile
    left outer join
        searches_last_market
        on sess_mobile.user_id = searches_last_market.user_id
    left outer join
        sess_mobile_last_market
        on sess_mobile_last_market.user_id = sess_mobile.user_id
    group by
        pdate,
        platform_type,
        platform,
        brand,
        market,
        source
),

searches_agg as (
    select
        avs.pdate,
        avs.platform_type,
        avs.platform,
        avs.brand,
        coalesce(searches_last_market.market, avs.market, 'N/D') as market,
        if(
            avs.platform in ('ios', 'android'),
            'mobile',
            coalesce(sess_web.source, avs.source)
        ) as source,
        count(*) as searches,
        sum(if(avs.is_domestic, 1, 0)) as searches_domestic,
        sum(if(avs.is_domestic, 0, 1)) as searches_international,
        sum(if(avs.round_trip, 1, 0)) as searches_round_trip,
        sum(if(avs.with_children, 1, 0)) as searches_with_children,
        sum(if(avs.with_auth, 1, 0)) as searches_with_auth
    from
        (
            select pdate,
                case
                    when
                        referrer_host iregexp 'sdk'
                        or coalesce(source_kind, '') = 'white_label'
                    then 'other'
                    when source_kind in ('web', 'mobile_web', 'mobile')
                    then source_kind
                    when
                        referrer_host iregexp '^(iphone|ipad|phone\\.android|tablet\\.android)\\.(aviasales|jetradar|jetradar-arabic|wayaway)'
                    then 'mobile'
                    when
                        user_agent iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
                    then 'web'
                    when user_agent iregexp '(mobi(le)?|iphone|ipad|android)'
                    then 'mobile_web'
                    else 'other'
                end as platform_type,
                if(to_date(pdate)>='2023-06-01', user_platform,
                case
                    when referrer_host iregexp '(ipad|iphone)'
                        and referrer_host iregexp '(aviasales|jetradar|wayaway)'
                        and referrer_host not iregexp 'sdk'
                    then 'ios'
                    when referrer_host iregexp '(android)'
                        and referrer_host iregexp '(aviasales|jetradar|wayaway)'
                        and referrer_host not iregexp 'sdk'
                    then 'android'
                    when referrer_host not iregexp '(ipad|iphone|android)'
                        and referrer_host not iregexp '^(m.aviasales)'
                        and referrer_host iregexp '(aviasales|jetradar|wayaway)'
                    then 'desktop'
                    when referrer_host iregexp '^(m.aviasales)'
                            and user_agent iregexp '(ipad|iphone)'
                    then 'ios_web'
                    when referrer_host iregexp '^(m.aviasales)'
                            and user_agent iregexp '(android)'
                    then 'android_web'
                    else 'other'
                end) as platform,
                case
                    when brand = 'WA' or referrer_host iregexp 'wayaway'
                    then 'wayaway'
                    when referrer_host iregexp 'aviasales'
                    then 'aviasales'
                    when referrer_host iregexp 'jetradar'
                    then 'jetradar'
                    else 'other'
                end as brand,
                coalesce(mobile_token, auid) user_id,
                round_trip,
                children + infants > 0 with_children,
                user_id is not null with_auth,
                ifnull(
                    if(
                        origin_country_iata in ('KX', 'RU'),
                        'RU',
                        origin_country_iata
                    ),
                    ''
                ) = ifnull(
                    if(
                        destination_country_iata in ('KX', 'RU'),
                        'RU',
                        destination_country_iata
                    ),
                    ''
                ) is_domestic,
                market,
                case
                    when referrer_host iregexp '(phone|ipad|android)'
                        or marker iregexp '^(mobile_app)'
                    then 'mobile'
                    when affiliate_marker in (29086,15468)
                        or (searches.marker iregexp '^(29086|15468)' and searches.market = 'ru')
                    then 'ppc_stoyan'
                    when affiliate_marker = 186703
                        or (searches.marker iregexp '^(186703)' and searches.market != 'ru')
                    then 'ppc_nm'
                    when tp_external.tp_marker is not null then 'affiliate'
                    when searches.marker_type in ('marketing', 'direct') then searches.marker_type
                    when marker iregexp '(google|yandex|rambler|mail|yahoo|bing|duckduckgo|vkontakte|facebook|instagram|telegram|twitter|odnoklassniki|vk.com)'
                    then 'organic'
                    when searches.referrer_host iregexp '(aviasales|jetradar|wayaway)'
                        and referrer_host iregexp '(sdk)'
                    then 'sdk'
                    when searches.referrer_host not iregexp '(aviasales|jetradar|wayaway)'
                    then 'wl'
                    else 'other'
                end source,
                created_at
            from avia.searches
            left outer join
                (
                    select marker as tp_marker
                    from tp.affiliates_info
                    where user_type != 'internal'
                ) as tp_external
                on avia.searches.affiliate_marker = tp_external.tp_marker
            where
                pdate between
                '{{ macros.ds_add(ds, params.partition_lookback) }}'
                and '{{ ds }}'
                and coalesce(marker, '') != 'internal-dshaker'
                and not coalesce(marked_as_bot, false)
                and referrer_host is not null
        ) as avs
    left outer join
        searches_last_market on avs.user_id = searches_last_market.user_id
    left outer join
        sess_web
        on
            sess_web.user_id = avs.user_id
            and avs.created_at > sess_web.start_ts
            and avs.created_at <= sess_web.end_ts
    group by
        pdate,
        platform_type,
        platform,
        brand,
        market,
        source
),

clicks_agg as (
    select
        avc.pdate,
        avc.platform_type,
        avc.platform,
        avc.brand,
        coalesce(searches_last_market.market, avc.market, 'N/D') as market,
        if(
            avc.platform in ('ios', 'android'),
            'mobile',
            coalesce(sess_web.source, avc.source)
        ) as source,
        count(*) as clicks,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then cpc_profit
                else 0
            end
        ) as cpc_profit,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then cpc_profit * cur.rate
                else 0
            end
        ) as cpc_profit_usd,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then 1
                else 0
            end
        ) as cpc_clicks,
        -- domestic vs international --
        sum(
            if(is_domestic, 1, 0)
        ) as clicks_domestic,
        sum(
            if(is_domestic, 0, 1)
        ) as clicks_international,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then if(is_domestic, cpc_profit, 0)
                else 0
            end
        ) as cpc_profit_domestic,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then if(is_domestic, 0, cpc_profit)
                else 0
            end
        ) as cpc_profit_international,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then if(is_domestic, cpc_profit * cur.rate, 0)
                else 0
            end
        ) as cpc_profit_usd_domestic,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then if(is_domestic, 0, cpc_profit * cur.rate)
                else 0
            end
        ) as cpc_profit_usd_international,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then if(is_domestic, 1, 0)
                else 0
            end
        ) as cpc_clicks_domestic,
        sum(
            case
                when clickmeter_ok.click_id is not null
                    then if(is_domestic, 0, 1)
                else 0
            end
        ) as cpc_clicks_international
    from
        (
            select pdate,
                case
                    when
                        coalesce(search_referrer_host, referrer_host) iregexp 'sdk'
                        or coalesce(source_kind, '') = 'white_label'
                    then 'other'
                    when source_kind in ('web', 'mobile_web', 'mobile')
                    then source_kind
                    when
                        coalesce(search_referrer_host, referrer_host) iregexp '^(iphone|ipad|phone\\.android|tablet\\.android)\\.(aviasales|jetradar|jetradar-arabic|wayaway)'
                    then 'mobile'
                    when
                        user_agent iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
                    then 'web'
                    when user_agent iregexp '(mobi(le)?|iphone|ipad|android)'
                    then 'mobile_web'
                    else 'other'
                end as platform_type,
                if(to_date(pdate)>='2023-06-01', user_platform,
                    case
                        when coalesce(search_referrer_host, referrer_host) iregexp '(ipad|iphone)'
                        and coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)'
                        and coalesce(search_referrer_host, referrer_host) not iregexp 'sdk' then 'ios'
                        when coalesce(search_referrer_host, referrer_host) iregexp '(android)'
                        and coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)'
                        and coalesce(search_referrer_host, referrer_host) not iregexp 'sdk' then 'android'
                        when coalesce(search_referrer_host, referrer_host) not iregexp '(ipad|iphone|android)'
                        and coalesce(search_referrer_host, referrer_host) not iregexp '^(m.aviasales)'
                        and coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)' then 'desktop'
                        when coalesce(search_referrer_host, referrer_host) iregexp '^(m.aviasales)'
                        and user_agent iregexp '(ipad|iphone)' then 'ios_web'
                        when coalesce(search_referrer_host, referrer_host) iregexp '^(m.aviasales)'
                        and user_agent iregexp '(android)' then 'android_web'
                        else 'other'
                    end
                ) as platform,
                case
                    when brand = 'WA' or coalesce(search_referrer_host, referrer_host) iregexp 'wayaway' then 'wayaway'
                    when coalesce(search_referrer_host, referrer_host) iregexp 'aviasales' then 'aviasales'
                    when coalesce(search_referrer_host, referrer_host) iregexp 'jetradar' then 'jetradar'
                    else 'other'
                end as brand,
                coalesce(mobile_token, auid) user_id,
                click_id,
                affiliate_marker,
                ifnull(if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata), '') = ifnull(if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata), '') is_domestic,
                cpc_profit,
                adults + infants + children passengers,
                market,
                case
                    when coalesce(search_referrer_host, referrer_host) iregexp '(phone|ipad|android)' OR marker iregexp '^(mobile_app)' then 'mobile'
                    when affiliate_marker in (29086,15468) or (marker iregexp '^(29086|15468)' and market = 'ru') then 'ppc_stoyan'
                    when affiliate_marker = 186703 or (marker iregexp '^(186703)' and market != 'ru') then 'ppc_nm'
                    when tp_external.tp_marker is not null then 'affiliate'
                    when marker_type in ('marketing', 'direct') then marker_type
                    when marker iregexp '(google|yandex|rambler|mail|yahoo|bing|duckduckgo|vkontakte|facebook|instagram|telegram|twitter|odnoklassniki|vk.com)' then 'organic'
                    when coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)' and coalesce(search_referrer_host, referrer_host) iregexp '(sdk)' then 'sdk'
                    when coalesce(search_referrer_host, referrer_host) not iregexp '(aviasales|jetradar|wayaway)' then 'wl'
                    else 'other'
                end source,
                created_at
            from avia.clicks
            left outer join
                (
                    select marker as tp_marker
                    from tp.affiliates_info
                    where user_type != 'internal'
                ) as tp_external
                on affiliate_marker = tp_external.tp_marker
            where
                pdate between
                '{{ macros.ds_add(ds, params.partition_lookback) }}'
                and '{{ ds }}'
        ) as avc
    left outer join searches_last_market
        on avc.user_id = searches_last_market.user_id
    left outer join
        sess_web
        on
            sess_web.user_id = avc.user_id
            and avc.created_at > sess_web.start_ts
            and avc.created_at <= sess_web.end_ts
    left outer join
        (
            select
                click_id,
                max(suspicions_click_flag) as suspicions_click_flag
            from
                (
                    select
                        click_id,
                        max(if(is_spider, 1, 0)) as suspicions_click_flag
                    from avia.clickmeter_clicks
                    where
                        pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
                        and '{{ ds }}'
                    group by
                        click_id

                    union all

                    select
                        click_id,
                        max(if(is_bot, 1, 0)) as suspicions_click_flag
                    from avia.scaleo_clicks
                    where
                        pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
                        and '{{ ds }}'
                    group by 1
                ) as scf
            group by click_id
            having max(suspicions_click_flag) = 0
        ) as clickmeter_ok
        on avc.click_id = clickmeter_ok.click_id
    left outer join
        (
            select
                pdate,
                rate
            from dictionary.currency_rates
            where src = 'RUB' and dst = 'USD'
        ) as cur
        on cur.pdate = avc.pdate
    group by
        pdate,
        platform_type,
        platform,
        brand,
        market,
        source
),

bookings_agg as (
    select
        avb.pdate,
        avb.platform_type,
        avb.platform,
        avb.brand,
        coalesce(searches_last_market.market, avb.market, 'N/D') as market,
        if(
            avb.platform in ('ios', 'android'),
            'mobile',
            coalesce(sess_web.source, avb.source)
        ) as source,
        count(*) as bookings,
        coalesce(sum(profit_amount), 0) as profit,
        coalesce(sum(price_amount), 0) as price,
        coalesce(sum(profit_amount_usd), 0) as profit_usd,
        coalesce(sum(price_amount_usd), 0) as price_usd,
        sum(
            case
                when avb.click_id is not null
                    then passengers
                else 0
            end
        ) as passengers,
        -- domestic vs international --
        coalesce(sum(if(is_domestic, 1, 0)), 0) as bookings_domestic,
        coalesce(sum(if(is_domestic, 0, 1)), 0) as bookings_international,
        coalesce(sum(if(is_domestic, profit_amount, 0)), 0) as profit_domestic,
        coalesce(sum(if(is_domestic, 0, profit_amount)), 0)
            as profit_international,
        coalesce(sum(if(is_domestic, price_amount, 0)), 0) as price_domestic,
        coalesce(sum(if(is_domestic, 0, price_amount)), 0)
            as price_international,
        coalesce(sum(if(is_domestic, profit_amount_usd, 0)), 0)
            as profit_usd_domestic,
        coalesce(sum(if(is_domestic, 0, profit_amount_usd)), 0)
            as profit_usd_international,
        coalesce(sum(if(is_domestic, price_amount_usd, 0)), 0)
            as price_usd_domestic,
        coalesce(sum(if(is_domestic, 0, price_amount_usd)), 0)
            as price_usd_international,
        sum(
            case
                when avb.click_id is not null
                    then if(is_domestic, passengers, 0)
                else 0
            end
        ) as passengers_domestic,
        sum(
            case
                when avb.click_id is not null
                    then if(is_domestic, 0, passengers)
                else 0
            end
        ) as passengers_international
    from (
        select to_date(booked_at) as pdate,
                case
                    when
                        coalesce(search_referrer_host, click_referrer_host) iregexp 'sdk'
                        or coalesce(search_source_kind, click_source_kind, '') = 'white_label'
                    then 'other'
                    when coalesce(search_source_kind, click_source_kind) in ('web', 'mobile_web', 'mobile')
                    then coalesce(search_source_kind, click_source_kind)
                    when
                        coalesce(search_referrer_host, click_referrer_host) iregexp '^(iphone|ipad|phone\\.android|tablet\\.android)\\.(aviasales|jetradar|jetradar-arabic|wayaway)'
                    then 'mobile'
                    when
                        user_agent iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
                    then 'web'
                    when user_agent iregexp '(mobi(le)?|iphone|ipad|android)'
                    then 'mobile_web'
                    else 'other'
                end as platform_type,
                if(to_date(booked_at)>='2023-06-01', user_platform,
                    case
                        when coalesce(search_referrer_host, click_referrer_host) iregexp '(ipad|iphone)'
                            and coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)'
                            and coalesce(search_referrer_host, click_referrer_host) not iregexp 'sdk' then 'ios'
                        when coalesce(search_referrer_host, click_referrer_host) iregexp '(android)'
                            and coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)'
                            and coalesce(search_referrer_host, click_referrer_host) not iregexp 'sdk' then 'android'
                        when coalesce(search_referrer_host, click_referrer_host) not iregexp '(ipad|iphone|android)'
                            and coalesce(search_referrer_host, click_referrer_host) not iregexp '^(m.aviasales)'
                            and coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)' then 'desktop'
                        when coalesce(search_referrer_host, click_referrer_host) iregexp '^(m.aviasales)'
                                and user_agent iregexp '(ipad|iphone)' then 'ios_web'
                        when coalesce(search_referrer_host, click_referrer_host) iregexp '^(m.aviasales)'
                                and user_agent iregexp '(android)' then 'android_web'
                        else 'other'
                    end
                ) as platform,
                case
                    when brand = 'WA' or coalesce(search_referrer_host, click_referrer_host) iregexp 'wayaway' then 'wayaway'
                    when coalesce(search_referrer_host, click_referrer_host) iregexp 'aviasales' then 'aviasales'
                    when coalesce(search_referrer_host, click_referrer_host) iregexp 'jetradar' then 'jetradar'
                    else 'other'
                end as brand,
                coalesce(mobile_token, search_auid, click_auid) user_id,
                ifnull(if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata), '') = ifnull(if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata), '') is_domestic,
                adults + infants + children passengers,
                market,
                case
                    when coalesce(search_referrer_host, click_referrer_host) iregexp '(phone|ipad|android)'
                        or coalesce(click_marker, search_marker) iregexp '^(mobile_app)' then 'mobile'
                    when click_affiliate_marker in (29086,15468) or (coalesce(click_marker, search_marker) iregexp '^(29086|15468)' and market = 'ru') then 'ppc_stoyan'
                    when click_affiliate_marker = 186703 or (coalesce(click_marker, search_marker) iregexp '^(186703)' and market != 'ru') then 'ppc_nm'
                    when tp_external.tp_marker is not null then 'affiliate'
                    when marker_type in ('marketing', 'direct') then marker_type
                    when coalesce(click_marker, search_marker) iregexp '(google|yandex|rambler|mail|yahoo|bing|duckduckgo|vkontakte|facebook|instagram|telegram|twitter|odnoklassniki|vk.com)' then 'organic'
                    when coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)' and coalesce(search_referrer_host, click_referrer_host) iregexp '(sdk)'  then 'sdk'
                    when coalesce(search_referrer_host, click_referrer_host) not iregexp '(aviasales|jetradar|wayaway)' then 'wl'
                    else 'other'
                end source,
                coalesce(created_at, click_created_at, search_created_at) created_at,
                click_id,
                booking_id,
                profit_amount,
                price_amount,
                profit_amount_usd,
                price_amount_usd
        from integrations.bookings
        left outer join
            (
                select marker as tp_marker
                from tp.affiliates_info
                where user_type != 'internal'
            ) as tp_external
            on
                integrations.bookings.click_affiliate_marker
                = tp_external.tp_marker
        where
            to_date(booked_at)
            between '{{ macros.ds_add(ds, params.partition_lookback) }}'
            and '{{ ds }}'
            and state = 'paid'
    ) as avb
    left outer join
        searches_last_market
        on avb.user_id = searches_last_market.user_id
    left outer join
        sess_web
        on
            sess_web.user_id = avb.user_id
            and avb.created_at > sess_web.start_ts
            and avb.created_at <= sess_web.end_ts
    group by
        pdate,
        platform_type,
        platform,
        brand,
        market,
        source
),

scb_agg as (
    select
        coalesce(
            searches_agg.pdate,
            clicks_agg.pdate,
            bookings_agg.pdate
        ) as pdate,
        coalesce(
            searches_agg.platform_type,
            clicks_agg.platform_type,
            bookings_agg.platform_type
        ) as platform_type,
        coalesce(
            searches_agg.platform,
            clicks_agg.platform,
            bookings_agg.platform
        ) as platform,
        coalesce(searches_agg.brand, clicks_agg.brand, bookings_agg.brand)
            as brand,
        coalesce(
            searches_agg.market,
            clicks_agg.market,
            bookings_agg.market
        ) as market,
        coalesce(searches_agg.source, clicks_agg.source, bookings_agg.source)
            as source,
        coalesce(searches_agg.searches, 0) as searches,
        coalesce(searches_agg.searches_round_trip, 0) as searches_round_trip,
        coalesce(searches_agg.searches_with_children, 0)
            as searches_with_children,
        coalesce(searches_agg.searches_with_auth, 0) as searches_with_auth,
        coalesce(clicks_agg.clicks, 0) as clicks,
        coalesce(bookings_agg.bookings, 0) as bookings,
        coalesce(bookings_agg.profit, 0) as profit,
        coalesce(bookings_agg.profit_usd, 0) as profit_usd,
        coalesce(bookings_agg.price, 0) as price,
        coalesce(bookings_agg.price_usd, 0) as price_usd,
        coalesce(bookings_agg.passengers, 0) as passengers,
        coalesce(clicks_agg.cpc_profit, 0) as cpc_profit,
        coalesce(clicks_agg.cpc_profit_usd, 0) as cpc_profit_usd,
        coalesce(clicks_agg.cpc_clicks, 0) as cpc_clicks,
        -- domestic vs international
        coalesce(searches_agg.searches_domestic, 0) as searches_domestic,
        coalesce(searches_agg.searches_international, 0)
            as searches_international,
        coalesce(clicks_agg.clicks_domestic, 0) as clicks_domestic,
        coalesce(clicks_agg.clicks_international, 0) as clicks_international,
        coalesce(bookings_agg.bookings_domestic, 0) as bookings_domestic,
        coalesce(bookings_agg.bookings_international, 0)
            as bookings_international,
        coalesce(bookings_agg.profit_domestic, 0) as profit_domestic,
        coalesce(bookings_agg.profit_international, 0) as profit_international,
        coalesce(bookings_agg.price_domestic, 0) as price_domestic,
        coalesce(bookings_agg.price_international, 0) as price_international,
        coalesce(bookings_agg.profit_usd_domestic, 0)
            as profit_usd_domestic,
        coalesce(bookings_agg.profit_usd_international, 0)
            as profit_usd_international,
        coalesce(bookings_agg.price_usd_domestic, 0)
            as price_usd_domestic,
        coalesce(bookings_agg.price_usd_international, 0)
            as price_usd_international,
        coalesce(bookings_agg.passengers_domestic, 0)
            as passengers_domestic,
        coalesce(bookings_agg.passengers_international, 0)
            as passengers_international,
        coalesce(clicks_agg.cpc_profit_domestic, 0)
            as cpc_profit_domestic,
        coalesce(clicks_agg.cpc_profit_international, 0)
            as cpc_profit_international,
        coalesce(clicks_agg.cpc_profit_usd_domestic, 0)
            as cpc_profit_usd_domestic,
        coalesce(clicks_agg.cpc_profit_usd_international, 0)
            as cpc_profit_usd_international,
        coalesce(clicks_agg.cpc_clicks_domestic, 0) as cpc_clicks_domestic,
        coalesce(clicks_agg.cpc_clicks_international, 0)
            as cpc_clicks_international
    from searches_agg
    full outer join clicks_agg
        on
            clicks_agg.platform_type = searches_agg.platform_type
            and clicks_agg.platform = searches_agg.platform
            and clicks_agg.brand = searches_agg.brand
            and clicks_agg.market = searches_agg.market
            and clicks_agg.source = searches_agg.source
            and clicks_agg.pdate = searches_agg.pdate
    full outer join bookings_agg
        on
            bookings_agg.platform_type = searches_agg.platform_type
            and bookings_agg.platform = searches_agg.platform
            and bookings_agg.brand = searches_agg.brand
            and bookings_agg.market = searches_agg.market
            and bookings_agg.source = searches_agg.source
            and bookings_agg.pdate = searches_agg.pdate
),

installs_agg as (
    select
        pdate,
        'mobile' as platform_type,
        case
            when app_id in ('id498958864', 'id606870241', 'id1112584191')
                then 'ios'
            when app_id in ('ru.aviasales', 'com.jetradar')
                then 'android'
            else 'other'
        end as platform,
        case
            when app_name iregexp 'wayaway'
            then 'wayaway'
            when app_id = 'com.jetradar' and i.pdate >= '2022-06-27'
            then 'wayaway'
            -- from release (27 Jun?) to 3rd Aug Android WayAway has wrong name
            -- (Aviasales) in AppsFlyer data
            when app_id in ('id498958864', 'ru.aviasales')
            then 'aviasales'
            when app_id in ('com.jetradar', 'id606870241', 'id1112584191')
            then 'jetradar'
            when app_id = 'id1112584191'
            then 'jetradar'
            else 'other'
        end as brand,
        coalesce(searches_last_market.market, country_code, 'N/D') market,
        'mobile' source,
        count(distinct appsflyer_device_id) installs
    from
        ( -- installs new--
            select
                appsflyer_id as appsflyer_device_id,
                customer_user_id as token,
                case
                    when
                        city in (
                            'Sevastopol',
                            "Sevastopol'",
                            'Simferopol',
                            'Yalta',
                            'Ialta',
                            'Sudak',
                            'Feodosiya',
                            'Feodosiia',
                            'Yevpatoriia',
                            'Fruktove',
                            'Bakhchisaray',
                            'Bakhchisaray',
                            'Kerch',
                            'Shchyolkino',
                            'Saki',
                            'Alushta',
                            'Dzhankoy',
                            'Gurzuf',
                            'Hurzuf',
                            'Bilohirsk',
                            'Ayvazovskoye',
                            'Saky',
                            'Vidradne',
                            'Staryi Krym',
                            'Simeiz',
                            'Alupka',
                            'Novomykolaivka',
                            'Vynohradne',
                            'Molodizhne',
                            'Bakhchysarai',
                            'Koreiz',
                            'Crimea',
                            'Masandra',
                            "Armians'K",
                            'Vesele',
                            'Haspra',
                            'Dzhankoy',
                            'Livadiia',
                            'Livadiya'
                        )
                        then 'ru'
                    else lower(country_code)
                end as country_code,
                to_date(install_time) as pdate,
                app_id,
                app_name
            from appsflyer.avia_installs
            where
                to_date(install_time) between
                '{{ macros.ds_add(ds, params.partition_lookback) }}'
                and '{{ ds }}'
                and app_id in (
                    'id606870241',
                    'com.jetradar',
                    'id1112584191',
                    'id498958864',
                    'ru.aviasales'
                )
                and event_name = 'install'
                and customer_user_id is not null
        ) as i
    left outer join searches_last_market
        on searches_last_market.user_id = i.token
    group by
        pdate,
        platform_type,
        platform,
        brand,
        market,
        source
)

insert overwrite table {{ params.schema }}.{{ params.relation }}
partition (pdate)
select
    coalesce(sess_agg.platform_type, scb_agg.platform_type, installs_agg.platform_type) as platform_type,
    coalesce(sess_agg.platform, scb_agg.platform, installs_agg.platform)                as platform,
    coalesce(sess_agg.brand, scb_agg.brand, installs_agg.brand)                         as brand,
    coalesce(sess_agg.market, scb_agg.market, installs_agg.market)                      as market,
    coalesce(sess_agg.source, scb_agg.source, installs_agg.source)                      as source,
    cast(coalesce(scb_agg.searches, 0) as int)                                          as searches,
    cast(coalesce(scb_agg.clicks, 0) as int)                                            as clicks,
    cast(coalesce(scb_agg.bookings, 0) as int)                                          as bookings,
    cast(coalesce(scb_agg.profit, 0) as decimal(38, 2))                                 as profit,
    cast(coalesce(scb_agg.profit_usd, 0) as decimal(38, 2))                             as profit_usd,
    cast(coalesce(scb_agg.price, 0) as decimal(38, 2))                                  as price,
    cast(coalesce(scb_agg.price_usd, 0) as decimal(38, 2))                              as price_usd,
    cast(coalesce(scb_agg.passengers, 0) as int)                                        as passengers,
    cast(coalesce(scb_agg.cpc_profit, 0) as decimal(38, 2))                             as cpc_profit,
    cast(coalesce(scb_agg.cpc_profit_usd, 0) as decimal(38, 2))                         as cpc_profit_usd,
    cast(coalesce(scb_agg.cpc_clicks, 0) as int)                                        as cpc_clicks,
    cast(coalesce(sessions, 0) as int)                                                  as sessions,
    cast(coalesce(sessions_with_clicks, 0) as int)                                      as sessions_with_click,
    cast(coalesce(sessions_with_searches, 0) as int)                                    as sessions_with_search,
    cast(coalesce(installs, 0) as int)                                                  as installs,
    -- domestic vs international
    cast(coalesce(searches_domestic, 0) as int)                                         as searches_domestic,
    cast(coalesce(searches_international, 0) as int)                                    as searches_international,
    cast(coalesce(clicks_domestic, 0) as int)                                           as clicks_domestic,
    cast(coalesce(clicks_international, 0) as int)                                      as clicks_international,
    cast(coalesce(bookings_domestic, 0) as int)                                         as bookings_domestic,
    cast(coalesce(bookings_international, 0) as int)                                    as bookings_international,
    cast(coalesce(profit_domestic, 0) as decimal(38, 2))                                as profit_domestic,
    cast(coalesce(profit_international, 0) as decimal(38, 2))                           as profit_international,
    cast(coalesce(price_domestic, 0) as decimal(38, 2))                                 as price_domestic,
    cast(coalesce(price_international, 0) as decimal(38, 2))                            as price_international,
    cast(coalesce(profit_usd_domestic, 0) as decimal(38, 2))                            as profit_usd_domestic,
    cast(coalesce(profit_usd_international, 0) as decimal(38, 2))                       as profit_usd_international,
    cast(coalesce(price_usd_domestic, 0) as decimal(38, 2))                             as price_usd_domestic,
    cast(coalesce(price_usd_international, 0) as decimal(38, 2))                        as price_usd_international,
    cast(coalesce(passengers_domestic, 0) as int)                                       as passengers_domestic,
    cast(coalesce(passengers_international, 0) as int)                                  as passengers_international,
    cast(coalesce(cpc_profit_domestic, 0) as decimal(38, 2))                            as cpc_profit_domestic,
    cast(coalesce(cpc_profit_international, 0) as decimal(38, 2))                       as cpc_profit_international,
    cast(coalesce(cpc_profit_usd_domestic, 0) as decimal(38, 2))                        as cpc_profit_usd_domestic,
    cast(coalesce(cpc_profit_usd_international, 0) as decimal(38, 2))                   as cpc_profit_usd_internation,
    cast(coalesce(cpc_clicks_domestic, 0) as int)                                       as cpc_clicks_domestic,
    cast(coalesce(cpc_clicks_international, 0) as int)                                  as cpc_clicks_international,
    cast(coalesce(total_ses_length_min, 0) as decimal(38, 2))                           as total_ses_length_min,
    cast(coalesce(scb_agg.searches_round_trip, 0) as int)                               as searches_round_trip,
    cast(coalesce(scb_agg.searches_with_children, 0) as int)                            as searches_with_children,
    cast(coalesce(scb_agg.searches_with_auth, 0) as int)                                as searches_with_auth,
    coalesce(sess_agg.pdate, scb_agg.pdate, installs_agg.pdate)                         as pdate
from sess_agg
full outer join scb_agg
    on
        sess_agg.platform = scb_agg.platform
        and sess_agg.platform_type = scb_agg.platform_type
        and sess_agg.brand = scb_agg.brand
        and sess_agg.market = scb_agg.market
        and sess_agg.source = scb_agg.source
        and sess_agg.pdate = scb_agg.pdate
full outer join installs_agg
    on
        coalesce(sess_agg.platform_type, scb_agg.platform_type)
            = installs_agg.platform_type
        and coalesce(sess_agg.platform, scb_agg.platform)
            = installs_agg.platform
        and coalesce(sess_agg.brand, scb_agg.brand) = installs_agg.brand
        and coalesce(sess_agg.market, scb_agg.market) = installs_agg.market
        and coalesce(sess_agg.source, scb_agg.source) = installs_agg.source
        and coalesce(sess_agg.pdate, scb_agg.pdate) = installs_agg.pdate
order by
    pdate,
    platform_type,
    platform,
    brand,
    market,
    source;

compute incremental stats {{ params.schema }}.{{ params.relation }}
partition (pdate BETWEEN '{{ macros.ds_add(ds, params.boundary) }}' AND '{{ ds }}');