Untitled

WITH

/* amd_fact AS () ,*/

   

        

/* The main body of the model is below this line. */

        airbyte_fact_vwb AS (

            SELECT DISTINCT

                CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp

                , CAST('dv3'

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key

                ,

                        'vwb'

                     AS brand_key

                , 'dv3' AS platform_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key

                , CAST(date AS DATE) AS date

                , CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id

                , CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id

                , CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id

                , SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local

                , SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions

                , SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks

                , SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements

                , SUM( TRY_CAST(null AS DOUBLE )) AS reach

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals

                , SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start

                , SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25

                , SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50

                , SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75

                , SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook

            FROM vwg_iceberg.vwg_raw.vwb_dv3_raw_performance raw

           

            WHERE TRUE

                  AND CAST(date AS DATE)

                      BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

              GROUP BY 1,2,3,4,5,6,7,8,9,10,11

        )

            ,

        airbyte_fact_aud AS (

            SELECT DISTINCT

                CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp

                , CAST('dv3'

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key

                ,

                        'aud'

                     AS brand_key

                , 'dv3' AS platform_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key

                , CAST(date AS DATE) AS date

                , CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id

                , CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id

                , CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id

                , SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local

                , SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions

                , SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks

                , SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements

                , SUM( TRY_CAST(null AS DOUBLE )) AS reach

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals

                , SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start

                , SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25

                , SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50

                , SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75

                , SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook

            FROM vwg_iceberg.vwg_raw.aud_dv3_raw_performance raw

           

            WHERE TRUE

                  AND CAST(date AS DATE)

                      BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

              GROUP BY 1,2,3,4,5,6,7,8,9,10,11

        )

            ,

        airbyte_fact_por AS (

            SELECT DISTINCT

                CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp

                , CAST('dv3'

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key

                ,

                        'por'

                     AS brand_key

                , 'dv3' AS platform_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key

                , CAST(date AS DATE) AS date

                , CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id

                , CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id

                , CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id

                , SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local

                , SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions

                , SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks

                , SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements

                , SUM( TRY_CAST(null AS DOUBLE )) AS reach

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals

                , SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start

                , SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25

                , SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50

                , SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75

                , SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook

            FROM vwg_iceberg.vwg_raw.por_dv3_raw_performance raw

           

            WHERE TRUE

                  AND CAST(date AS DATE)

                      BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

              GROUP BY 1,2,3,4,5,6,7,8,9,10,11

        )

            ,

        airbyte_fact_sea AS (

            SELECT DISTINCT

                CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp

                , CAST('dv3'

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key

                ,

                        'sea'

                     AS brand_key

                , 'dv3' AS platform_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key

                , CAST(date AS DATE) AS date

                , CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id

                , CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id

                , CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id

                , SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local

                , SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions

                , SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks

                , SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements

                , SUM( TRY_CAST(null AS DOUBLE )) AS reach

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals

                , SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start

                , SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25

                , SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50

                , SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75

                , SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook

            FROM vwg_iceberg.vwg_raw.sea_dv3_raw_performance raw

           

            WHERE TRUE

                  AND CAST(date AS DATE)

                      BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

              GROUP BY 1,2,3,4,5,6,7,8,9,10,11

        )

            ,

        airbyte_fact_ben AS (

            SELECT DISTINCT

                CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp

                , CAST('dv3'

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key

                ,

                        'ben'

                     AS brand_key

                , 'dv3' AS platform_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key

                , CAST(date AS DATE) AS date

                , CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id

                , CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id

                , CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id

                , SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local

                , SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions

                , SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks

                , SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements

                , SUM( TRY_CAST(null AS DOUBLE )) AS reach

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals

                , SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start

                , SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25

                , SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50

                , SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75

                , SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook

            FROM vwg_iceberg.vwg_raw.ben_dv3_raw_performance raw

           

            WHERE TRUE

                  AND CAST(date AS DATE)

                      BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

              GROUP BY 1,2,3,4,5,6,7,8,9,10,11

        )

            ,

        airbyte_fact_sko AS (

            SELECT DISTINCT

                CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp

                , CAST('dv3'

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key

                ,

                        'sko'

                     AS brand_key

                , 'dv3' AS platform_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key

                , CAST(date AS DATE) AS date

                , CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id

                , CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id

                , CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id

                , SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local

                , SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions

                , SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks

                , SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements

                , SUM( TRY_CAST(null AS DOUBLE )) AS reach

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals

                , SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start

                , SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25

                , SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50

                , SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75

                , SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook

            FROM vwg_iceberg.vwg_raw.sko_dv3_raw_performance raw

           

            WHERE TRUE

                  AND CAST(date AS DATE)

                      BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

              GROUP BY 1,2,3,4,5,6,7,8,9,10,11

        )

            ,

        airbyte_fact_lcv AS (

            SELECT DISTINCT

                CAST(current_timestamp AS TIMESTAMP(6) WITH TIME ZONE) AS updated_timestamp

                , CAST('dv3'

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA')

                  || '_' || DATE_FORMAT(CAST(date AS DATE), '%Y%m%d') AS VARCHAR ) AS composite_fact_key

                ,

                        'lcv'

                     AS brand_key

                , 'dv3' AS platform_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_account_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_campaign_key

                , 'dv3' || '_' || COALESCE(CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR), 'NA') AS platform_universal_key

                , CAST(date AS DATE) AS date

                , CAST(CAST(ROUND(TRY_CAST(advertiser_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS ac_account_id

                , CAST(CAST(ROUND(TRY_CAST(campaign_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS cm_campaign_id

                , CAST(CAST(ROUND(TRY_CAST(insertion_order_id AS DOUBLE )) AS BIGINT) AS VARCHAR) AS un_universal_id

                , SUM( TRY_CAST(media_cost_advertiser_currency AS DOUBLE PRECISION)) AS spend_local

                , SUM( CAST( ROUND( TRY_CAST(impressions AS DOUBLE )) AS BIGINT )) AS impressions

                , SUM( CAST( ROUND( TRY_CAST(clicks AS DOUBLE )) AS BIGINT )) AS clicks

                , SUM( CAST( ROUND( TRY_CAST(engagements AS DOUBLE )) AS BIGINT )) AS engagements

                , SUM( TRY_CAST(null AS DOUBLE )) AS reach

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS likes

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS shares

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS comments

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS visits_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS users_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS quality_arrivals

                , SUM( CAST( ROUND( TRY_CAST(starts_video AS DOUBLE )) AS BIGINT )) AS video_played_start

                , SUM( CAST( ROUND( TRY_CAST(first_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_25

                , SUM( CAST( ROUND( TRY_CAST(midpoint_views_video AS DOUBLE )) AS BIGINT )) AS video_played_50

                , SUM( CAST( ROUND( TRY_CAST(third_quartile_views_video AS DOUBLE )) AS BIGINT )) AS video_played_75

                , SUM( CAST( ROUND( TRY_CAST(complete_views_video AS DOUBLE )) AS BIGINT )) AS video_played_100

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS true_view

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_complete_unique

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_trim_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_engine_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_exterior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_interior_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_equipment_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS config_accessories_start

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_qualified

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_test_drive

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_finance_calculator

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_brochure_download

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_find_retailer

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_contact_us

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_info_request

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_dealer_contact_sent

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_newsletter

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_onfacebook

                , SUM( CAST( ROUND( TRY_CAST(null AS DOUBLE )) AS BIGINT )) AS leads_allfacebook

            FROM vwg_iceberg.vwg_raw.lcv_dv3_raw_performance raw

           

            WHERE TRUE

                  AND CAST(date AS DATE)

                      BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

              GROUP BY 1,2,3,4,5,6,7,8,9,10,11

        )

 

,

final_fact AS (

    select * from airbyte_fact_vwb

    UNION ALL

    select * from airbyte_fact_aud

    UNION ALL

    select * from airbyte_fact_por

    UNION ALL

    select * from airbyte_fact_sea

    UNION ALL

    select * from airbyte_fact_ben

    UNION ALL

    select * from airbyte_fact_sko

    UNION ALL

    select * from airbyte_fact_lcv

),

 

redshift_fact AS (

    SELECT redshift_raw.*

    FROM vwg_iceberg.vwg_rawredshift.all_all_admin_redshiftfactperformance AS redshift_raw

    LEFT JOIN

        (

            SELECT composite_fact_key FROM final_fact GROUP BY 1

        ) airbyte_fact

        ON redshift_raw.composite_fact_key = airbyte_fact.composite_fact_key

    WHERE

        redshift_raw.platform_key = 'dv3'

            AND redshift_raw.date

                BETWEEN DATE_ADD('day', -1 * 31, current_timestamp) and current_timestamp

        AND airbyte_fact.composite_fact_key IS NULL

)

 

SELECT fact.*

FROM

    (

        SELECT * FROM final_fact WHERE composite_fact_key IS NOT NULL

        UNION ALL

        SELECT * FROM redshift_fact WHERE composite_fact_key IS NOT NULL

    ) fact

INNER JOIN

    (

        SELECT

            platform_account_key

            , MAX(brand_key) AS brand_key

        FROM

            vwg_iceberg.vwg_silver.all_dv3_dim_account

        GROUP BY 1

    ) ac

    ON fact.platform_account_key = ac.platform_account_key

        AND fact.brand_key = ac.brand_key

en