data-dwh-dbt-project/models/intermediate/cross/int_deals_consolidation.sql

with
    int_xero__contacts as (select * from {{ ref("int_xero__contacts") }}),
    int_core__unified_user as (select * from {{ ref("int_core__unified_user") }}),
    int_hubspot__deal as (select * from {{ ref("int_hubspot__deal") }}),

    core_deal_counts as (
        select id_deal, count(distinct company_name) as core_company_name_count
        from int_core__unified_user
        where id_deal is not null
        group by id_deal
    ),

    ranked_core_deals as (
        select
            u.id_deal,
            u.company_name as core_company_name,
            c.core_company_name_count,
            row_number() over (
                partition by u.id_deal order by u.created_at_utc
            ) as row_num
        from int_core__unified_user u
        left join core_deal_counts c on u.id_deal = c.id_deal
        where u.id_deal is not null
    ),
    deals_core as (
        select id_deal, core_company_name, core_company_name_count
        from ranked_core_deals
        where row_num = 1
    ),

    hubspot_deal_counts as (
        select id_deal, count(distinct deal_name) as hubspot_deal_name_count
        from int_hubspot__deal
        where id_deal is not null
        group by id_deal
    ),

    ranked_hubspot_deals as (
        select
            h.id_deal,
            h.deal_name as hubspot_deal_name,
            c.hubspot_deal_name_count,
            row_number() over (
                partition by h.id_deal order by h.created_at_utc
            ) as row_num
        from int_hubspot__deal h
        left join hubspot_deal_counts c on h.id_deal = c.id_deal
        where h.id_deal is not null
    ),
    deals_hubspot as (
        select id_deal, hubspot_deal_name, hubspot_deal_name_count
        from ranked_hubspot_deals
        where row_num = 1
    ),

    xero_contact_counts as (
        select id_deal, count(distinct contact_name) as xero_contact_name_count
        from int_xero__contacts
        where id_deal is not null
        group by id_deal
    ),

    ranked_xero_deals as (
        select
            x.id_deal,
            x.contact_name as xero_contact_name,
            c.xero_contact_name_count,
            row_number() over (
                partition by x.id_deal order by x.updated_at_utc
            ) as row_num
        from int_xero__contacts x
        left join xero_contact_counts c on x.id_deal = c.id_deal
        where x.id_deal is not null
    ),
    deals_xero as (
        select id_deal, xero_contact_name, xero_contact_name_count
        from ranked_xero_deals
        where row_num = 1
    )

select
    *,
    case when dc.id_deal is not null then true else false end as is_deal_in_core,
    case when dh.id_deal is not null then true else false end as is_deal_in_hubspot,
    case when dx.id_deal is not null then true else false end as is_deal_in_xero
from deals_core dc
full outer join deals_hubspot dh using (id_deal)
full outer join deals_xero dx using (id_deal)