UPDATE buckets_tagset tagset SET _cached_stats_last_updated = last_updated FROM ( SELECT join_tagset.id as join_tagset_id, MAX(daily_spend.db_spend_last_updated) AS last_updated FROM buckets_tagset join_tagset JOIN vayner_portfolio portfolio on portfolio.id = join_tagset.portfolio_id JOIN clients_account account on account.portfolio_id = portfolio.id JOIN clients_campaign campaign on account.id = campaign.account_id JOIN campaign_daily_spend daily_spend ON daily_spend.campaign_id = campaign.id WHERE join_tagset.portfolio_id = 579374 AND source_type = 4 AND (join_tagset._cached_stats_last_updated IS NULL OR daily_spend.db_spend_last_updated > join_tagset._cached_stats_last_updated) GROUP BY join_tagset.id) AS max_last_updated_table WHERE tagset.id = max_last_updated_table.join_tagset_id;

Date: 2024-10-30 03:44:05
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.97.99

x Hide

SELECT "api_data_custom_platform_info_to_custom_platform_info"."main_platform_info_id", "api_data_custom_platform_info_to_custom_platform_info"."associated_platform_info_id" FROM "api_data_custom_platform_info_to_custom_platform_info" INNER JOIN "api_data_custom_platform_info_to_custom_platform_info_conne8ea3" ON ("api_data_custom_platform_info_to_custom_platform_info"."id" = "api_data_custom_platform_info_to_custom_platform_info_conne8ea3"."customplatforminfotocustomplatforminfo_id") WHERE "api_data_custom_platform_info_to_custom_platform_info_conne8ea3"."connectionrule_id" IN (1585);

Date: 2024-10-30 16:11:56
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.122.149

x Hide

SELECT "api_data_goplatformobjecttocustomplatforminfo"."go_platform_object_id", "api_data_goplatformobjecttocustomplatforminfo"."custom_platform_info_id" FROM "api_data_goplatformobjecttocustomplatforminfo" INNER JOIN "api_data_goplatformobjecttocustomplatforminfo_connection_rules" ON ("api_data_goplatformobjecttocustomplatforminfo"."id" = "api_data_goplatformobjecttocustomplatforminfo_connection_rules"."goplatformobjecttocustomplatforminfo_id") WHERE "api_data_goplatformobjecttocustomplatforminfo_connection_rules"."connectionrule_id" IN (1884);

Date: 2024-10-30 10:40:38
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.124.7

x Hide

WITH cf_names AS (SELECT MAX(CASE WHEN cf.name = 'Spend' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as spend_name, MAX(CASE WHEN cf.name = 'Impressions' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as imp_name, MAX(CASE WHEN cf.name = 'Link Clicks' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as click_name, MAX(CASE WHEN cf.name = 'Video Views' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as vv_name, MAX(CASE WHEN cf.name = 'Video Completions' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as vc_name, MAX(CASE WHEN cf.name = 'CP Exec Field 1' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as ef1_name, MAX(CASE WHEN cf.name = 'CP Exec Field 2' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as ef2_name, fs.platform_id, cf.portfolio_id FROM api_data_crossfieldtoplatformmetric AS cf_to_metric LEFT JOIN api_data_crossfield AS cf ON cf.id = cf_to_metric.cross_field_id LEFT JOIN api_data_field AS fs ON fs.id = cf_to_metric.field_id WHERE cf.name = 'Spend' AND cf.is_fixture_data = FALSE OR (cf.name = 'Impressions' AND cf.is_fixture_data = FALSE) OR (cf.name = 'Link Clicks' AND cf.is_fixture_data = FALSE) OR (cf.name = 'Video Views' AND cf.is_fixture_data = FALSE) OR (cf.name = 'Video Completions' AND cf.is_fixture_data = FALSE) OR (cf.name = 'CP Exec Field 1' AND cf.is_fixture_data = FALSE) OR (cf.name = 'CP Exec Field 2' AND cf.is_fixture_data = FALSE) GROUP BY fs.platform_id, cf.portfolio_id) SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, CASE WHEN data_info.data::json->>'advertising_channel_type' IS NULL THEN CAST(data_info.data::json->>'advertising_channel' AS TEXT) ELSE CAST(data_info.data::json->>'advertising_channel_type' AS TEXT) END AS channel, NULL AS exec_field_2, CASE WHEN data_info.data::json->>'bidding_strategy_type' IS NULL THEN CAST(data_info.data::json->>'bid_strategy_type' AS TEXT) ELSE CAST(data_info.data::json->>'bidding_strategy_type' AS TEXT) END AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_go_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_go_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_go_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, CAST(data_info.data::json->>'advertising_channel_type' AS TEXT) AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_tt_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_tt_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_tt_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_pi_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_pi_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_pi_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_li_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_li_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_li_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_sc_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_sc_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_sc_info AS data_info ON data_info.sc_platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, CAST(data_info.data::json->>'campaign_type' AS TEXT) AS channel, NULL AS exec_field_2, CASE WHEN CAST(CAST(data_info.data::json->>'bidding_scheme' AS JSON)::json->>'type' AS TEXT) IS NULL THEN 'ManualCpc' ELSE CAST(CAST(data_info.data::json->>'bidding_scheme' AS JSON)::json->>'type' AS TEXT) END AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_bn_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_bn_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_bn_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'performance_goal_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_dbm_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_dbm_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_dbm_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CASE WHEN CAST(data_info.data::json ->> 'bidding_strategy' AS TEXT) IS NULL THEN 'ruleBased' ELSE CAST(data_info.data::json ->> 'bidding_strategy' AS TEXT) END AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_am_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_am_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_am_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(CAST(data_info.data::json->>'optimization' AS JSON)::json->>'goal' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_amd_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_amd_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_amd_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT line_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(account.name, '|', '') AS account_name, platform.name AS platform_name, CONCAT(platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platform.id AS tracer_platform_id, SUM(line_stats.spend) AS obj_spend, SUM(line_stats.spend / CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(line_stats.Impressions) AS obj_impressions, SUM(line_stats.link_clicks) AS obj_link_clicks, SUM(line_stats.video_views) AS obj_video_views, SUM(line_stats.video_view_p100) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective' AS TEXT) AS objective, camp_info.currency AS obj_currency_id, camp_info.id AS cpgn_id FROM (SELECT tweet_stats.date AS date, SUM(CAST(tweet_stats.data::json->>'spend' AS FLOAT)) AS spend, SUM(CAST(tweet_stats.data::json->>'impressions' AS FLOAT)) AS Impressions, SUM(CAST(tweet_stats.data::json->>'url_clicks' AS FLOAT)) AS link_clicks, SUM(CAST(tweet_stats.data::json->>'video_total_views' AS FLOAT)) AS video_views, SUM(CAST(tweet_stats.data::json->>'video_views_100' AS FLOAT)) AS video_view_p100, line_object_id.id AS line_id, tweet_stats.campaign_id AS campaign_id FROM api_data_tw_stats AS tweet_stats LEFT JOIN api_data_tw_platformobject AS object_id ON tweet_stats.platform_object_id = object_id.id LEFT JOIN api_data_tw_map AS tweet_map ON tweet_map.level_1_id = object_id.id LEFT JOIN api_data_tw_platformobject AS line_object_id ON tweet_map.level_2_id = line_object_id.id GROUP BY line_id, date, campaign_id) AS line_stats LEFT JOIN api_data_tw_info AS data_info ON data_info.platform_object_id = line_stats.line_id LEFT JOIN clients_campaign AS camp_info ON camp_info.id = line_stats.campaign_id LEFT JOIN clients_account AS account ON account.id = camp_info.account_id LEFT JOIN platforms_platform AS platform ON platform.id = camp_info.platform_id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = account.portfolio_id LEFT JOIN vayner_exchangerate AS fx ON line_stats.date = fx.date AND camp_info.currency = fx.currency WHERE line_stats.date > '2024-07-31' AND line_stats.date <= '2024-10-29' GROUP BY line_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, REPLACe(breakdown_bucket.name, '|', '') AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_fb_campaign_stats_platformbreakdown_placement AS cpgn_stats LEFT JOIN platforms_platformbreakdownbucket AS breakdown_bucket ON breakdown_bucket.id = cpgn_stats.platform_breakdown_bucket_id LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cp_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, 'custom_platform' AS account_name, platform.name AS platform_name, CONCAT(platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platform.id AS tracer_platform_id, SUM(CAST(cp_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cp_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cp_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cp_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cp_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cp_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, cp_info.data::json->>(cf_names.ef1_name) AS channel, cp_info.data::json->>(cf_names.ef2_name) AS exec_field_2, 'custom_platform' AS objective, cp_info.currency AS currency_id, cp_info.id AS cpgn_id FROM api_data_customplatformstats AS cp_stats LEFT JOIN api_data_customplatforminfo AS cp_info ON cp_stats.custom_platform_info_id = cp_info.id LEFT JOIN platforms_platform AS platform ON platform.id = cp_info.platform_id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = cp_info.portfolio_id LEFT JOIN vayner_exchangerate AS fx ON cp_stats.date = fx.date AND cp_info.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platform.id WHERE cp_stats.date > '2024-07-31' AND cp_stats.date <= '2024-10-29' AND cp_stats.spend_micro > 0 GROUP BY cp_stats.date, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, tracer_platform_id, platform.name, channel, objective, currency_id, cpgn_id, exec_field_2;

Date: 2024-10-30 08:15:05
Duration: 12m9s
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.117.211

WITH cf_names AS (SELECT MAX(CASE WHEN cf.name = 'Spend' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as spend_name, MAX(CASE WHEN cf.name = 'Impressions' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as imp_name, MAX(CASE WHEN cf.name = 'Link Clicks' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as click_name, MAX(CASE WHEN cf.name = 'Video Views' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as vv_name, MAX(CASE WHEN cf.name = 'Video Completions' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as vc_name, MAX(CASE WHEN cf.name = 'CP Exec Field 1' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as ef1_name, MAX(CASE WHEN cf.name = 'CP Exec Field 2' AND cf.is_fixture_data = 'FALSE' THEN fs.db_name ELSE NULL END) as ef2_name, fs.platform_id, cf.portfolio_id FROM api_data_crossfieldtoplatformmetric AS cf_to_metric LEFT JOIN api_data_crossfield AS cf ON cf.id = cf_to_metric.cross_field_id LEFT JOIN api_data_field AS fs ON fs.id = cf_to_metric.field_id WHERE cf.name = 'Spend' AND cf.is_fixture_data = FALSE OR (cf.name = 'Impressions' AND cf.is_fixture_data = FALSE) OR (cf.name = 'Link Clicks' AND cf.is_fixture_data = FALSE) OR (cf.name = 'Video Views' AND cf.is_fixture_data = FALSE) OR (cf.name = 'Video Completions' AND cf.is_fixture_data = FALSE) OR (cf.name = 'CP Exec Field 1' AND cf.is_fixture_data = FALSE) OR (cf.name = 'CP Exec Field 2' AND cf.is_fixture_data = FALSE) GROUP BY fs.platform_id, cf.portfolio_id) SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, CASE WHEN data_info.data::json->>'advertising_channel_type' IS NULL THEN CAST(data_info.data::json->>'advertising_channel' AS TEXT) ELSE CAST(data_info.data::json->>'advertising_channel_type' AS TEXT) END AS channel, NULL AS exec_field_2, CASE WHEN data_info.data::json->>'bidding_strategy_type' IS NULL THEN CAST(data_info.data::json->>'bid_strategy_type' AS TEXT) ELSE CAST(data_info.data::json->>'bidding_strategy_type' AS TEXT) END AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_go_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_go_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_go_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, CAST(data_info.data::json->>'advertising_channel_type' AS TEXT) AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_tt_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_tt_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_tt_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_pi_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_pi_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_pi_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_li_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_li_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_li_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_sc_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_sc_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_sc_info AS data_info ON data_info.sc_platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, CAST(data_info.data::json->>'campaign_type' AS TEXT) AS channel, NULL AS exec_field_2, CASE WHEN CAST(CAST(data_info.data::json->>'bidding_scheme' AS JSON)::json->>'type' AS TEXT) IS NULL THEN 'ManualCpc' ELSE CAST(CAST(data_info.data::json->>'bidding_scheme' AS JSON)::json->>'type' AS TEXT) END AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_bn_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_bn_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_bn_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'performance_goal_type' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_dbm_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_dbm_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_dbm_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CASE WHEN CAST(data_info.data::json ->> 'bidding_strategy' AS TEXT) IS NULL THEN 'ruleBased' ELSE CAST(data_info.data::json ->> 'bidding_strategy' AS TEXT) END AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_am_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_am_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_am_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(CAST(data_info.data::json->>'optimization' AS JSON)::json->>'goal' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_amd_campaign_stats AS cpgn_stats LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_amd_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_amd_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT line_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(account.name, '|', '') AS account_name, platform.name AS platform_name, CONCAT(platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platform.id AS tracer_platform_id, SUM(line_stats.spend) AS obj_spend, SUM(line_stats.spend / CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(line_stats.Impressions) AS obj_impressions, SUM(line_stats.link_clicks) AS obj_link_clicks, SUM(line_stats.video_views) AS obj_video_views, SUM(line_stats.video_view_p100) AS obj_video_view_p100, NULL AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective' AS TEXT) AS objective, camp_info.currency AS obj_currency_id, camp_info.id AS cpgn_id FROM (SELECT tweet_stats.date AS date, SUM(CAST(tweet_stats.data::json->>'spend' AS FLOAT)) AS spend, SUM(CAST(tweet_stats.data::json->>'impressions' AS FLOAT)) AS Impressions, SUM(CAST(tweet_stats.data::json->>'url_clicks' AS FLOAT)) AS link_clicks, SUM(CAST(tweet_stats.data::json->>'video_total_views' AS FLOAT)) AS video_views, SUM(CAST(tweet_stats.data::json->>'video_views_100' AS FLOAT)) AS video_view_p100, line_object_id.id AS line_id, tweet_stats.campaign_id AS campaign_id FROM api_data_tw_stats AS tweet_stats LEFT JOIN api_data_tw_platformobject AS object_id ON tweet_stats.platform_object_id = object_id.id LEFT JOIN api_data_tw_map AS tweet_map ON tweet_map.level_1_id = object_id.id LEFT JOIN api_data_tw_platformobject AS line_object_id ON tweet_map.level_2_id = line_object_id.id GROUP BY line_id, date, campaign_id) AS line_stats LEFT JOIN api_data_tw_info AS data_info ON data_info.platform_object_id = line_stats.line_id LEFT JOIN clients_campaign AS camp_info ON camp_info.id = line_stats.campaign_id LEFT JOIN clients_account AS account ON account.id = camp_info.account_id LEFT JOIN platforms_platform AS platform ON platform.id = camp_info.platform_id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = account.portfolio_id LEFT JOIN vayner_exchangerate AS fx ON line_stats.date = fx.date AND camp_info.currency = fx.currency WHERE line_stats.date > '2024-07-31' AND line_stats.date <= '2024-10-29' GROUP BY line_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cpgn_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, REPLACE(clients_account.name, '|', '') AS account_name, platforms_platform.name AS platform_name, CONCAT(platforms_platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platforms_platform.id AS tracer_platform_id, SUM(CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cpgn_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cpgn_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cpgn_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cpgn_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cpgn_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, REPLACe(breakdown_bucket.name, '|', '') AS channel, NULL AS exec_field_2, CAST(data_info.data::json->>'objective' AS TEXT) AS objective, clients_campaign.currency AS obj_currency_id, clients_campaign.id AS cpgn_id FROM api_data_fb_campaign_stats_platformbreakdown_placement AS cpgn_stats LEFT JOIN platforms_platformbreakdownbucket AS breakdown_bucket ON breakdown_bucket.id = cpgn_stats.platform_breakdown_bucket_id LEFT JOIN clients_campaign ON clients_campaign.id = cpgn_stats.campaign_id LEFT JOIN platforms_platform ON platforms_platform.id = clients_campaign.platform_id LEFT JOIN clients_account ON clients_campaign.account_id = clients_account.id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = clients_account.portfolio_id LEFT JOIN api_data_platformobject AS object_id ON object_id.db_id_foreign = clients_campaign.db_id_foreign LEFT JOIN api_data_info AS data_info ON data_info.platform_object_id = object_id.id LEFT JOIN vayner_exchangerate AS fx ON cpgn_stats.date = fx.date AND clients_campaign.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platforms_platform.id WHERE cpgn_stats.date > '2024-07-31' AND cpgn_stats.date <= '2024-10-29' GROUP BY cpgn_stats.date, platform_name, platform_portfolio, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, account_name, tracer_platform_id, channel, objective, obj_currency_id, cpgn_id, exec_field_2 UNION ALL SELECT cp_stats.date AS date, portfolio.id AS portfolio_id, REPLACE(portfolio.name, '|', '') AS portfolio_name, portfolio.archive_date AS portfolio_archive_date, portfolio.created AS portfolio_created_date, portfolio.active_date AS portfolio_active_date, 'custom_platform' AS account_name, platform.name AS platform_name, CONCAT(platform.name, '__', REPLACE(portfolio.name, '|', '')) AS platform_portfolio, platform.id AS tracer_platform_id, SUM(CAST(cp_stats.data::json->>(cf_names.spend_name) AS FLOAT)) AS obj_spend, SUM((CAST(cp_stats.data::json->>(cf_names.spend_name) AS FLOAT))/CAST(CASE WHEN fx.value IS NULL THEN 1.0 ELSE fx.value/1000000.0 END AS FLOAT)) AS obj_spend_dollars, SUM(CAST(cp_stats.data::json->>(cf_names.imp_name) AS FLOAT)) AS obj_impressions, SUM(CAST(cp_stats.data::json->>(cf_names.click_name) AS FLOAT)) AS obj_link_clicks, SUM(CAST(cp_stats.data::json->>(cf_names.vv_name) AS FLOAT)) AS obj_video_views, SUM(CAST(cp_stats.data::json->>(cf_names.vc_name) AS FLOAT)) AS obj_video_view_p100, cp_info.data::json->>(cf_names.ef1_name) AS channel, cp_info.data::json->>(cf_names.ef2_name) AS exec_field_2, 'custom_platform' AS objective, cp_info.currency AS currency_id, cp_info.id AS cpgn_id FROM api_data_customplatformstats AS cp_stats LEFT JOIN api_data_customplatforminfo AS cp_info ON cp_stats.custom_platform_info_id = cp_info.id LEFT JOIN platforms_platform AS platform ON platform.id = cp_info.platform_id LEFT JOIN vayner_portfolio AS portfolio ON portfolio.id = cp_info.portfolio_id LEFT JOIN vayner_exchangerate AS fx ON cp_stats.date = fx.date AND cp_info.currency = fx.currency LEFT JOIN cf_names ON cf_names.portfolio_id = portfolio.id AND cf_names.platform_id = platform.id WHERE cp_stats.date > '2024-07-31' AND cp_stats.date <= '2024-10-29' AND cp_stats.spend_micro > 0 GROUP BY cp_stats.date, portfolio.id, portfolio_name, portfolio_archive_date, portfolio_created_date, portfolio_active_date, tracer_platform_id, platform.name, channel, objective, currency_id, cpgn_id, exec_field_2 ;

Date: 2024-10-30 08:03:00
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.117.211

x Hide

UPDATE "data_sets_dataset" SET "data_status" = 2 WHERE "data_sets_dataset"."id" IN ( SELECT U0."id" FROM "data_sets_dataset" U0 INNER JOIN "api_data_datasettoplatform" U1 ON (U0."id" = U1."data_set_id") LEFT OUTER JOIN "data_sets_datasettocolumn" U3 ON (U0."id" = U3."data_set_id") LEFT OUTER JOIN "data_sets_datasetsourcetoportfolio" U5 ON (U0."id" = U5."data_set_id") LEFT OUTER JOIN "vayner_portfolio" U6 ON (U5."portfolio_id" = U6."id") LEFT OUTER JOIN "clients_account" U7 ON (U6."id" = U7."portfolio_id") LEFT OUTER JOIN "clients_campaign" U8 ON (U7."id" = U8."account_id") LEFT OUTER JOIN "campaign_daily_spend" U9 ON (U8."id" = U9."campaign_id") WHERE (NOT (U0."data_status" = 5) AND NOT (U0."_cached_lifetime_end_date" <= '2024-09-29'::date AND U0."_cached_lifetime_end_date" IS NOT NULL AND U0."date_range_source" = 2) AND U1."platform_id" = 6 AND U0."data_source_type" = 4 AND U0."data_status" = 0 AND NOT CASE WHEN (U3."platform_breakdown_id" IS NOT NULL) THEN true ELSE false END) GROUP BY U0."id", U0."last_refreshed" HAVING U0."last_refreshed" < (MAX(U9."db_spend_last_updated")));

Date: 2024-10-30 11:00:23
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.184.106

x Hide

SELECT COUNT(*) FROM ( SELECT CASE WHEN "data_sets_datasettocolumn"."platform_breakdown_id" IS NOT NULL THEN true ELSE false END AS "has_platform_breakdown" FROM "data_sets_dataset" INNER JOIN "api_data_datasettoplatform" ON ("data_sets_dataset"."id" = "api_data_datasettoplatform"."data_set_id") LEFT OUTER JOIN "data_sets_datasettocolumn" ON ("data_sets_dataset"."id" = "data_sets_datasettocolumn"."data_set_id") LEFT OUTER JOIN "data_sets_datasetsourcetoportfolio" ON ("data_sets_dataset"."id" = "data_sets_datasetsourcetoportfolio"."data_set_id") LEFT OUTER JOIN "vayner_portfolio" ON ("data_sets_datasetsourcetoportfolio"."portfolio_id" = "vayner_portfolio"."id") LEFT OUTER JOIN "clients_account" ON ("vayner_portfolio"."id" = "clients_account"."portfolio_id") LEFT OUTER JOIN "clients_campaign" ON ("clients_account"."id" = "clients_campaign"."account_id") LEFT OUTER JOIN "campaign_daily_spend" ON ("clients_campaign"."id" = "campaign_daily_spend"."campaign_id") WHERE (NOT ("data_sets_dataset"."data_status" = 5) AND NOT ("data_sets_dataset"."_cached_lifetime_end_date" <= '2024-09-29'::date AND "data_sets_dataset"."_cached_lifetime_end_date" IS NOT NULL AND "data_sets_dataset"."date_range_source" = 2) AND "api_data_datasettoplatform"."platform_id" = 2 AND "data_sets_dataset"."data_source_type" = 4 AND "data_sets_dataset"."data_status" = 0 AND NOT CASE WHEN ("data_sets_datasettocolumn"."platform_breakdown_id" IS NOT NULL) THEN true ELSE false END) GROUP BY "data_sets_dataset"."id", 1, "data_sets_dataset"."last_refreshed" HAVING "data_sets_dataset"."last_refreshed" < (MAX("campaign_daily_spend"."db_spend_last_updated"))) subquery;

Date: 2024-10-30 11:10:50
Duration: 5m24s
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.185.159

SELECT COUNT(*) FROM ( SELECT CASE WHEN "data_sets_datasettocolumn"."platform_breakdown_id" IS NOT NULL THEN true ELSE false END AS "has_platform_breakdown" FROM "data_sets_dataset" INNER JOIN "api_data_datasettoplatform" ON ("data_sets_dataset"."id" = "api_data_datasettoplatform"."data_set_id") LEFT OUTER JOIN "data_sets_datasettocolumn" ON ("data_sets_dataset"."id" = "data_sets_datasettocolumn"."data_set_id") LEFT OUTER JOIN "data_sets_datasetsourcetoportfolio" ON ("data_sets_dataset"."id" = "data_sets_datasetsourcetoportfolio"."data_set_id") LEFT OUTER JOIN "vayner_portfolio" ON ("data_sets_datasetsourcetoportfolio"."portfolio_id" = "vayner_portfolio"."id") LEFT OUTER JOIN "clients_account" ON ("vayner_portfolio"."id" = "clients_account"."portfolio_id") LEFT OUTER JOIN "clients_campaign" ON ("clients_account"."id" = "clients_campaign"."account_id") LEFT OUTER JOIN "campaign_daily_spend" ON ("clients_campaign"."id" = "campaign_daily_spend"."campaign_id") WHERE (NOT ("data_sets_dataset"."data_status" = 5) AND NOT ("data_sets_dataset"."_cached_lifetime_end_date" <= '2024-09-29'::date AND "data_sets_dataset"."_cached_lifetime_end_date" IS NOT NULL AND "data_sets_dataset"."date_range_source" = 2) AND "api_data_datasettoplatform"."platform_id" = 2 AND "data_sets_dataset"."data_source_type" = 4 AND "data_sets_dataset"."data_status" = 0 AND NOT CASE WHEN ("data_sets_datasettocolumn"."platform_breakdown_id" IS NOT NULL) THEN true ELSE false END) GROUP BY "data_sets_dataset"."id", 1, "data_sets_dataset"."last_refreshed" HAVING "data_sets_dataset"."last_refreshed" < (MAX("campaign_daily_spend"."db_spend_last_updated"))) subquery;

Date: 2024-10-30 11:05:57
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.185.159

x Hide

SELECT DISTINCT map.level_3_id, map.level_3_id, campaign.id FROM api_data_go_map map JOIN api_data_go_platformobject po ON map.level_3_id = po.id JOIN ( SELECT DISTINCT campaign.id, campaign.db_id_foreign FROM clients_campaign campaign WHERE campaign.platform_id = 17 AND (campaign.end_date IS NULL OR campaign.end_date >= '2020-01-12')) campaign ON po.db_id_foreign = campaign.db_id_foreign WHERE map.level_4_id IN (136175019, 136175020, 136175022, 2243106642, 1364322596, 83497388, 83497365, 83497366, 83497375, 83497378, 83497389, 83497425, 83497443, 83497447, 84030476, 84030491, 136175018, 2700795276, 455958444, 2700795277, 2616817488, 1364322595, 83497330, 83497347, 83497350, 83497358, 83497360, 83497373, 83497381, 83497422, 83497448, 83497451, 83497472, 83497473, 84030449, 84030457, 136174999, 136175000, 136175001, 136175002, 191479432, 212327455, 214183236, 276113199, 288076582, 370845399, 379913453, 379913454, 389250694, 455958440, 455958441, 455958443, 459410422, 459410423, 620967041, 975182800, 1308666244, 1364329813, 1621737789, 1849118540, 1933895585, 2475562501, 2475562502, 370845600, 455958442, 2475955326, 2193159280, 370845400, 1849118539, 1209612137) UNION SELECT DISTINCT partial_map.level_3_id, partial_map.level_3_id, campaign.id FROM api_data_go_partial_map partial_map JOIN api_data_go_platformobject po ON partial_map.level_3_id = po.id JOIN ( SELECT DISTINCT campaign.id, campaign.db_id_foreign FROM clients_campaign campaign WHERE campaign.platform_id = 17 AND (campaign.end_date IS NULL OR campaign.end_date >= '2020-01-12')) campaign ON po.db_id_foreign = campaign.db_id_foreign WHERE partial_map.level_4_id IN (136175019, 136175020, 136175022, 2243106642, 1364322596, 83497388, 83497365, 83497366, 83497375, 83497378, 83497389, 83497425, 83497443, 83497447, 84030476, 84030491, 136175018, 2700795276, 455958444, 2700795277, 2616817488, 1364322595, 83497330, 83497347, 83497350, 83497358, 83497360, 83497373, 83497381, 83497422, 83497448, 83497451, 83497472, 83497473, 84030449, 84030457, 136174999, 136175000, 136175001, 136175002, 191479432, 212327455, 214183236, 276113199, 288076582, 370845399, 379913453, 379913454, 389250694, 455958440, 455958441, 455958443, 459410422, 459410423, 620967041, 975182800, 1308666244, 1364329813, 1621737789, 1849118540, 1933895585, 2475562501, 2475562502, 370845600, 455958442, 2475955326, 2193159280, 370845400, 1849118539, 1209612137);

Date: 2024-10-30 15:15:20
Duration: 0ms

x Hide

SELECT "api_data_ttplatformobjecttocustomplatforminfo"."tt_platform_object_id", "api_data_ttplatformobjecttocustomplatforminfo"."custom_platform_info_id" FROM "api_data_ttplatformobjecttocustomplatforminfo" INNER JOIN "api_data_ttplatformobjecttocustomplatforminfo_connection_rules" ON ("api_data_ttplatformobjecttocustomplatforminfo"."id" = "api_data_ttplatformobjecttocustomplatforminfo_connection_rules"."ttplatformobjecttocustomplatforminfo_id") WHERE "api_data_ttplatformobjecttocustomplatforminfo_connection_rules"."connectionrule_id" IN (1090);

Date: 2024-10-30 10:54:01
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.172.9

x Hide

SELECT DISTINCT map.level_3_id, map.level_3_id, campaign.id FROM api_data_go_map map JOIN api_data_go_platformobject po ON map.level_3_id = po.id JOIN ( SELECT DISTINCT campaign.id, campaign.db_id_foreign FROM clients_campaign campaign WHERE campaign.platform_id = 17 AND (campaign.end_date IS NULL OR campaign.end_date >= '2022-01-11') ) campaign ON po.db_id_foreign = campaign.db_id_foreign WHERE map.level_3_id IN (978143435,978143436,978143437,978143438,978143439,978143440,978143441,978143442,978143443,978143444,978143445,978143446,978143447,978143448,978143449,978143450,978143451,978143452,978143453,978143454,978143455,978143456,978143457,978143458,978143459,978143460,978143461,978143462,978143463,978143464,978143465,978143466,978143467,978143468,978143469,978143470,978143471,978143472,978143473,978143474,980103618,980103619,980103620,980103621,980103622,980103623,980103636,980103637,980103638,980103639,980103640,980103641,980103657,980103658,980103659,980103660,980103661,980103662,980103663,980103664,983034131,983034132,983034133,983034134,983034135,983034136,983034137,983034138,983034139,983034140,983034159,983034160,983034161,983034162,983034186,983034187,983034188,983034189,983034190,983034191,990920621,990920622,990920623,990920624,990920625,990920626,990920627,990920628,990920629,990920630,990920631,990920632,990920633,990920634,990920635,990920636,990920637,990920638,990920639,990920640,1158127505,1192696048,1192696050,1192696054,1192696055,1192696056,1192696057,1192696058,1192696059,1192696060,1192696061,1192696062,1192696063,1192696064,1192696065,1192696066,1192696067,1192696068,1192696069,1192696070,1192696071,1192696072,1192696073,1192696074,1192696075,1192696076,1192696077,1192696078,1192696079,1192696080,1192696081,1192696082,1192696083,1192696084,1192696085,1192696086,1192696087,1192696088,1192696089,1192696090,1192696091,1192696092,1192696093,1192696094,1192696095,1192696096,1192696097,1192696098,1192696099,1192696100,1192696101,1192696102,1192696103,1192696104,1192696105,1192696106,1192696107,1192696108,1192696109,1192696110,1192696111,1192696112,1192696113,1192696114,1192696115,1192696116,1192696117,1192696118,1192696119,1192696120,1192696121,1192696122,1192696123,1192696124,1192696125,1192696126,1192696127,1192696128,1192696129,1192696130,1192696131,1192696132,1192696133,1192696134,1192696135,1192696136,1192696137,1192696138,1192696139,1192696140,1192696141,1192696142,1192696143,1192696144,1192696145,1192696146,1192696147,1192696148,1192696149,1192696150,1192696151,1192696152,1192696153,1192696154,1192696155,1192696156,1192696157,1192696158,1192696159,1192696163,1192696164,1192696165,1192696167,1192696168,1192696169,1194248256,1194248257,1194248258,1194248259,1194248260,1194248261,1194248262,1194248263,1194248264,1194248265,1194248266,1194248267,1194248268,1194248269,1194248270,1194248271,1194248272,1194248273,1194248274,1194248313,1194248314,1194248315,1194248316,1194248317,1194248318,1194248319,1194248320,1194248321,1194248322,1194248323,1194248324,1194248325,1194248326,1194248327,1194248328,1194248329,1194248330,1194248366,1194248367,1194248368,1194248369,1194248370,1194248371,1194248372,1194248373,1194248374,1194248375,1194248376,1194248377,1194248378,1194248379,1194248380,1195015961,1195015962,1198078806,1198078807,1198078808,1198078809,1198078810,1198078811,1198078812,1198078813,1198078814,1198078815,1198078816,1198078817,1198078818,1198078819,1198078820,1198078821,1198078822,1198078823,1198078824,1198078825,1198078826,1198078827,1198078828,1198078829,1198078830,1198078831,1198078832,1198078833,1198078834,1198078835,1198078836,1198078837,1198078838,1198078839,1198078840,1198078841,1198078842,1198078843,1198078844,1198078885,1198078886,1198078887,1198078888,1198078889,1198078890,1198078891,1198078892,1198078893,1198078894,1198078895,1198078896,1198078897,1198078898,1198078899,1198078900,1198078901,1198078902,1198078903,1198078904,1198078905,1198078906,1198078907,1198078908,1198078909,1198078910,1198078911,1198078912,1198078913,1198078914,1198078915,1198078916,1198078917,1198078918,1198078919,1198078920,1198078921,1198078922,1198078923,1198078924,1198078925,1198078926,1198078927,1198078928,1198078929,1198078930,1198078931,1198078932,1198078933,1198078934,1198078935,1198078936,1198079006,1198079007,1198079008,1198079009,1198079010,1198079011,1198079012,1198079013,1198079014,1198079015,1198079016,1198079017,1198079018,1198079019,1198079020,1198079021,1198079022,1198079023,1198079024,1198079025,1198079026,1198079027,1198079028,1198079029,1198079030,1198079031,1198079032,1198079033,1198079034,1198079035,1198079036,1198079037,1198079038,1198079039,1198079040,1198079041,1198079042,1198079043,1198079044,1198079045,1198079046,1198079047,1198079048,1198079049,1198079050,1198079051,1198079052,1198079053,1198079054,1198079055,1198079056,1198079057,1198079058,1198079059,1198079060,1198079061,1198079062,1280605742,1280605743,1281224748,1281224749,1594008291,1594008292,1594008293,1594008294,1594008295,1594008296,1594008297,1594008298,1594008299,1594008300,1594008301,1594008302,1594008303,1594008304,1594008305,1594008306,1594008307,1594008308,1594008309,1594008310,1598363176,1598363177,1640393707,1643511201,1645041136,1645041137,1645041138,1645041139,1645041140,1645041141,1645041142,1645041143,1645041144,1645041145,1645041146,1645041147,1645041148,1645041151,1645041152,1645041153,1645041154,1645041155,1645041156,1645041159,1645041160,1645041161,1645041162,1645041163,1645041164,1645041165,1645041166,1645041167,1645041168,1645041169,1645041170,1645041172,1645041173,1645041174,1645041175,1645041176,1645041177,1645041178,1645041179,1645041181,1645041182,1663211488,1663211489,1663211490,1663211491,1663211492,1663211493,1663211494,1663211495,1663211496,1663211497,1663211498,1663211499,1663211500,1663211501,1663211502,1663211503,1663211504,1663211505,1663211506,1663211507,1675194406,1675194407,1676492359,1676492360,1676492361,1676492362,1680386646,1720531225,1740804294,1783363172,1783363173,1812031554,1812031555,1826826303,1826826304,1858770643,1858770644,1872671701,1872671702,1887190212,1890165452,1890165453,1890165454,1892148255,1892148256,1892148257,1892148258,1892148259,1892148260,1892148261,1892148769,1892148770,1892148771,1892148772,1892148773,1892148774,1892148775,1892148776,1892148777,1892148778,1892148779,1892148780,1892148781,1892148782,1892148783,1892148784,1902187359,1902187386,1902616488,1902616490,1917827017,1920291587,1920760403,1920761068,1921264358,1921264359,1932950232,1932950233,1933916591,1934376566,1935765141,1953307251,1953307256,1954743538,1954743539,1954743540,1954743541,1957553466,1959443440,1959443441,1964174483,1964174484,1964630423,1964630425,1964630426,1964630997,1971807883,1971807884,1979411672,1979411673,1979411674,1979411675,1979411676,1979411677,1984139858,1985559124,1985559125,1986525592,1986525593,1986995995,1988405028,1988405029,1988405030,1991740483,1991740484,2026537021,2026537022,2026537023,2026537024,2026537025,2035967447,2035967448,2035967449,2035967450,2035967451,2037007664,2043946560,2043946561,2043946562,2050411221,2050411222,2050411223,2050411224,2050411225,2056852832,2056852833,2058623702,2058623703,2058623704,2058623705,2058623706,2058623707,2058623708,2058623709,2058623710,2060385861,2102001087,2102409153,2102409154,2102409155,2102409156,2104786162,2104786163,2104786164,2104786165,2104786166,2105176592,2112725307,2112725308,2112725309,2112725310,2127095678,2127095679,2127512193,2127512194,2127512195,2132910163,2132910164,2132910165,2132910166,2132910167,2132910168,2135864602,2144371391,2147326384,2147326385,2147550971,2147550972,2162663416,2162663417,2162663535,2163087820,2163087821,2163087822,2165256724,2165259212,2169937757,2175978487,2175978488,2175978489,2175978490,2177684430,2177684431,2180691285,2182844601,2182844602,2182844603,2198065208,2198065209,2198065210,2198065211,2198065212,2198065213,2198065266,2198065267,2198065268,2198065269,2198065270,2198065271,2198518194,2199748985,2200287855,2200287856,2200715907,2200715909,2200715910,2200715911,2204788904,2205216562,2205216563,2205216564,2205216565,2205216566,2206122513,2206122514,2206122515,2206122516,2206122517,2206122518,2206122519,2206122520,2206122521,2235617938,2243201627,2243201628,2243201629,2243201630,2255354629,2255354630,2255354631,2255354632,2255354633,2255354634,2267969064,2267969065,2267969066,2267969067,2269385060,2269385061,2270374236,2270374237,2294658893,2294658894,2298466923,2298466924,2298466925,2298466926,2310008053,2310008054,2310008055,2310008056,2310008057,2310008058,2310984721,2310984722,2310984723,2310984724,2310984725,2310984726,2315432960,2315432961,2315432962,2315432963,2316898093,2316898094,2356759039,2356759040,2359718761,2359718762,2359718763,2359718764,2359718765,2359718766,2359718767,2359718768,2359718769,2359718770,2359718771,2359718772,2373024640,2373024641,2373024642,2384403700,2384403701,2391884997,2391884998,2391884999,2391885000,2391885001,2391885002,2391885003,2391885004,2391885005,2391885006,2391885007,2391885008,2391885009,2391885010,2391885011,2391885012,2448028411,2448028412,2448028413,2449498061,2449498062,2449498063,2449498064,2449498065,2449498066,2454396506,2454396507,2454396508,2454396509,2454396510,2454396511,2456253366,2456253367,2456253368,2456253369,2456253370,2456253371,2468937486,2468937487,2468937488,2468937489,2468937490,2468937491,2472958981,2472958982,2472958983,2474856191,2474856192,2474856193,2475975931,2475975932,2475975933,2475975934,2475975935,2475975936,2475975937,2475975938,2475975939,2481528460,2481528461,2481528462,2481549883,2481549884,2487010227,2487010228,2487761039,2487761040,2490764562,2490764563,2490764564,2490764565,2490764566,2490764567,2507450090,2507450091,2507450092,2531783208,2531783209,2531783210,2531783320,2531783521,2531783522,2531783523,2531786048,2531786049,2531786050,2531789928,2531789929,2531789930,2531789931,2531789932,2531789933,2532396952,2532398313,2532398314,2532400125,2532400126,2532400127,2532400128,2532400129,2532400130,2532400131,2532400132,2532400133,2532400134,2532400135,2533012792,2533018467,2533018468,2533018469,2533018470,2533018471,2541736684,2541736685,2541736686,2541736687,2541736688,2541736689,2548285019,2550261875,2552134724,2552134725,2552134726,2552134727,2552134728,2567163695,2567163696,2567163697,2581557725,2582206382,2582206383,2582206384,2582206385,2582206386,2582206387,2582206388,2582206389,2582209602,2582860530,2582860531,2582860532,2582860533,2583512067,2583512069,2586774595,2586774596,2586774597,2597488663,2610203076,2610203077,2611023782,2611023783,2611023784,2611023785,2611057034,2611057038,2613135705,2613135706,2613135708,2613135709,2613135710,2613135711,2613135712,2613206294,2613206302,2613206304,2613991451,2613991452,2613991453,2614060456,2614060458,2614060460,2614163610,2614163611,2615628295,2615628296,2615628297,2615628298,2616184412,2616184413,2616184414,2616295983,2616529129,2618660866,2618660867,2618660868,2618660869,2618660870,2618660871,2618660872,2618660873,2618719800,2618719803,2618719808,2618719809,2619606073,2619606074,2619606075,2619606076,2619606077,2619661912,2619661913,2620477372,2620477373,2620477374,2620477375,2620477376,2620477377,2620482769,2620482770,2620482771,2620482772,2620482773,2620482774,2620512869,2620512870,2620512871,2620512872,2620512873,2620512874,2620512875,2620512876,2620512877,2620528887,2620528888,2620528889,2620528890,2620528891,2620528892,2620528893,2620528894,2620575888,2620575891,2620575896,2620575897,2620575898,2620575899,2620575901,2620575903,2620592440,2620592443,2622285547,2622285548,2622285549,2622285550,2622285551,2622285552,2622285553,2622841132,2622841133,2622841638,2623091162,2623265563,2623997794,2626809087,2626809088,2626809089,2626809090,2626809091,2626809092,2626809093,2626809094,2626809095,2626809096,2626809097,2626809852,2626809853,2626809854,2626809855,2626809856,2626809857,2626809858,2626809859,2626809860,2626809861,2626809862,2626809863,2626809864,2626809865,2626809866,2626809867,2626809868,2626809869,2626809870,2626809871,2626817256,2626817257,2626817258,2626817259,2626817260,2626821514,2626821515,2626821516,2626822242,2626822243,2626822244,2626822245,2626822246,2628129067,2628129068,2628129069,2628176975,2628176979,2628517177,2628517178,2628517179,2628517180,2628517181,2628517182,2628517183,2628517184,2628517185,2628517186,2628919209,2628919210,2632046425,2632046426,2632046427,2632046428,2632046429,2632046430,2632046431,2632046432,2632046433,2632046434,2632046435,2633259817,2633259818,2633683091,2633683092,2633683093,2633683094,2633683095,2633683096,2635009384,2635009385,2635009386,2635009387,2635009388,2635246049,2635469247,2635469248,2635469249,2635469250,2635469251,2635469252,2635469253,2635469254,2635469255,2635469256,2635469257,2635469258,2635469259,2635469260,2635469261,2635469262,2637259038,2637259039,2637259040,2637259041,2637259042,2637259043,2637259044,2637259045,2637259046,2637259047,2637259048,2637259049,2637259050,2637259051,2637259052,2637259053,2639006669,2645076641,2645076642,2645076643,2645076644,2645076645,2645076646,2645076647,2645076648,2645076649,2645076650,2645076651,2645076652,2645077475,2645077476,2645077477,2645077478,2645077479,2645077480,2645077481,2645077482,2645077483,2645077484,2645077485,2645077486,2645077487,2645077488,2645077489,2645077490,2645077491,2645077492,2645077493,2645077494,2645924368,2645924369,2645924370,2645924371,2645924372,2648763251,2648940224,2649071921,2649071922,2649071923,2649071924,2649071925,2649071926,2649071927,2649071928,2649071929,2649071930,2649071931,2649071932,2649071933,2649071934,2649071935,2649071936,2649071937,2649071938,2649071939,2649071940,2649071941,2649071942,2649071943,2649071944,2649405477,2649405480,2649405482,2649405483,2649405487,2649484217,2649484218,2649484219,2649484220,2649647156,2649647167,2649836199,2650290428,2650295803,2651923027,2651923031,2651923032,2651923033,2651923034,2653715413,2654556719,2654556720,2654556721,2654556722,2654556723,2654556724,2654556725,2654556727,2654556728,2654556729,2654556730,2654556731,2654556732,2654556733,2656068014,2656068015,2656068016,2656068017,2656068018,2656068019,2656068020,2656068021,2656068022,2656068023,2656068024,2656068025,2656068026,2656068027,2656068028,2656068029,2656068030,2656068031,2656068032,2656068033,2656068034,2656068035,2656068036,2656068037,2656068381,2656068382,2656068383,2656068384,2656068385,2656068386,2656069452,2656069453,2656069454,2656069455,2656069456,2656079206,2656079207,2656079208,2656079209,2656079210,2656079211,2656079212,2656079213,2656079214,2656079215,2656079216,2656079217,2656079218,2656079219,2656079220,2656079221,2656079222,2656079223,2656079224,2656079225,2656258411,2656403096,2656403097,2656403098,2656403099,2656403100,2656403101,2656403102,2656403103,2656403104,2656403105,2656403106,2656403107,2657765265,2657765266,2657765267,2657765268,2657765269,2657765270,2657765271,2672171992,2672171993,2672171994,2672171995,2672171996,2672171997,2672171998,2672171999,2672172000,2672172001,2672172002,2672172003,2672172004,2672172005,2672172006,2672172007,2672172008,2672172009,2672172010,2672172011,2672172012,2672172013,2672172014,2672172015,2682030687,2682030688,2682030689,2682030690,2682030691,2682030692,2682030693,2682030694,2682030695,2682030696,2682030697,2682030698,2682030699,2682030700,2682030701,2682030702,2687124990,2687124991,2687124992,2687124993,2687124994,2687124995,2690793075,2690793076,2690793077,2690793078,2690793079,2690793080,2690793081,2690793082,2690793083,2690793084,2690793085,2690793086,2698355288,2698355289,2698355290,2698355291,2698355292,2698355293,2698355294,2698355295,2698355296,2698355297,2698355298,2698355299,2698355300,2698355301,2698355302,2698355303,2698355304,2698355305,2698355306,2698355307,2698355308,2698355309,2698355310,2698355311,2698355312,2698355313,2698355314,2698355315,2698355316,2698355317,2698355318,2698355319,2698355320,2698355321,2698355322,2698355323,2698355324,2698355325,2698355326,2698355327,2706361690,2706375234,2706375235,2706375236,2706375237,2706401943,2706401945,2706401946,2706401947,2706401948,2706401949,2706401950,2707083549,2707908598,2707908599,2707908600,2707908601,2707908602,2707908603,2707908604,2707908605,2707908606,2707911637,2707911638,2707911639,2707911640,2707911641,2707911642,2707911643,2707911644,2707911645,2707911646,2707986509,2707989547,2707989548,2708404398,2708404399,2708404403,2708404404,2708404405,2708404408,2708443420,2708486589,2708486590,2708486591,2708486592,2708486593,2708486594,2708986349,2708986350,2708986351,2708986352,2708986353,2708986354,2708986355,2708986357,2708986358,2708986359,2708986360,2708986361,2708986362,2708986363,2708986364,2708986365,2708986366,2708986367,2708986368,2708986369,2708986370,2708986371,2708986372,2708986373,2708986374,2708986375,2708986376,2708986377,2708986378,2708986379,2708986380,2708986381,2708986382,2708986383,2708986384,2708986385,2708986386,2708986387,2708986388,2708986389,2708986390,2708986391,2708986392,2708986393,2708986394,2709025454,2709025458,2709252595,2709252596,2709252597,2709252598,2709252599,2709252600,2709252601,2709252602,2709581724,2709581725,2709581726,2709581727,2709873912) UNION SELECT DISTINCT partial_map.level_3_id, partial_map.level_3_id, campaign.id FROM api_data_go_partial_map partial_map JOIN api_data_go_platformobject po ON partial_map.level_3_id = po.id JOIN ( SELECT DISTINCT campaign.id, campaign.db_id_foreign FROM clients_campaign campaign WHERE campaign.platform_id = 17 AND (campaign.end_date IS NULL OR campaign.end_date >= '2022-01-11') ) campaign ON po.db_id_foreign = campaign.db_id_foreign WHERE partial_map.level_3_id IN (978143435,978143436,978143437,978143438,978143439,978143440,978143441,978143442,978143443,978143444,978143445,978143446,978143447,978143448,978143449,978143450,978143451,978143452,978143453,978143454,978143455,978143456,978143457,978143458,978143459,978143460,978143461,978143462,978143463,978143464,978143465,978143466,978143467,978143468,978143469,978143470,978143471,978143472,978143473,978143474,980103618,980103619,980103620,980103621,980103622,980103623,980103636,980103637,980103638,980103639,980103640,980103641,980103657,980103658,980103659,980103660,980103661,980103662,980103663,980103664,983034131,983034132,983034133,983034134,983034135,983034136,983034137,983034138,983034139,983034140,983034159,983034160,983034161,983034162,983034186,983034187,983034188,983034189,983034190,983034191,990920621,990920622,990920623,990920624,990920625,990920626,990920627,990920628,990920629,990920630,990920631,990920632,990920633,990920634,990920635,990920636,990920637,990920638,990920639,990920640,1158127505,1192696048,1192696050,1192696054,1192696055,1192696056,1192696057,1192696058,1192696059,1192696060,1192696061,1192696062,1192696063,1192696064,1192696065,1192696066,1192696067,1192696068,1192696069,1192696070,1192696071,1192696072,1192696073,1192696074,1192696075,1192696076,1192696077,1192696078,1192696079,1192696080,1192696081,1192696082,1192696083,1192696084,1192696085,1192696086,1192696087,1192696088,1192696089,1192696090,1192696091,1192696092,1192696093,1192696094,1192696095,1192696096,1192696097,1192696098,1192696099,1192696100,1192696101,1192696102,1192696103,1192696104,1192696105,1192696106,1192696107,1192696108,1192696109,1192696110,1192696111,1192696112,1192696113,1192696114,1192696115,1192696116,1192696117,1192696118,1192696119,1192696120,1192696121,1192696122,1192696123,1192696124,1192696125,1192696126,1192696127,1192696128,1192696129,1192696130,1192696131,1192696132,1192696133,1192696134,1192696135,1192696136,1192696137,1192696138,1192696139,1192696140,1192696141,1192696142,1192696143,1192696144,1192696145,1192696146,1192696147,1192696148,1192696149,1192696150,1192696151,1192696152,1192696153,1192696154,1192696155,1192696156,1192696157,1192696158,1192696159,1192696163,1192696164,1192696165,1192696167,1192696168,1192696169,1194248256,1194248257,1194248258,1194248259,1194248260,1194248261,1194248262,1194248263,1194248264,1194248265,1194248266,1194248267,1194248268,1194248269,1194248270,1194248271,1194248272,1194248273,1194248274,1194248313,1194248314,1194248315,1194248316,1194248317,1194248318,1194248319,1194248320,1194248321,1194248322,1194248323,1194248324,1194248325,1194248326,1194248327,1194248328,1194248329,1194248330,1194248366,1194248367,1194248368,1194248369,1194248370,1194248371,1194248372,1194248373,1194248374,1194248375,1194248376,1194248377,1194248378,1194248379,1194248380,1195015961,1195015962,1198078806,1198078807,1198078808,1198078809,1198078810,1198078811,1198078812,1198078813,1198078814,1198078815,1198078816,1198078817,1198078818,1198078819,1198078820,1198078821,1198078822,1198078823,1198078824,1198078825,1198078826,1198078827,1198078828,1198078829,1198078830,1198078831,1198078832,1198078833,1198078834,1198078835,1198078836,1198078837,1198078838,1198078839,1198078840,1198078841,1198078842,1198078843,1198078844,1198078885,1198078886,1198078887,1198078888,1198078889,1198078890,1198078891,1198078892,1198078893,1198078894,1198078895,1198078896,1198078897,1198078898,1198078899,1198078900,1198078901,1198078902,1198078903,1198078904,1198078905,1198078906,1198078907,1198078908,1198078909,1198078910,1198078911,1198078912,1198078913,1198078914,1198078915,1198078916,1198078917,1198078918,1198078919,1198078920,1198078921,1198078922,1198078923,1198078924,1198078925,1198078926,1198078927,1198078928,1198078929,1198078930,1198078931,1198078932,1198078933,1198078934,1198078935,1198078936,1198079006,1198079007,1198079008,1198079009,1198079010,1198079011,1198079012,1198079013,1198079014,1198079015,1198079016,1198079017,1198079018,1198079019,1198079020,1198079021,1198079022,1198079023,1198079024,1198079025,1198079026,1198079027,1198079028,1198079029,1198079030,1198079031,1198079032,1198079033,1198079034,1198079035,1198079036,1198079037,1198079038,1198079039,1198079040,1198079041,1198079042,1198079043,1198079044,1198079045,1198079046,1198079047,1198079048,1198079049,1198079050,1198079051,1198079052,1198079053,1198079054,1198079055,1198079056,1198079057,1198079058,1198079059,1198079060,1198079061,1198079062,1280605742,1280605743,1281224748,1281224749,1594008291,1594008292,1594008293,1594008294,1594008295,1594008296,1594008297,1594008298,1594008299,1594008300,1594008301,1594008302,1594008303,1594008304,1594008305,1594008306,1594008307,1594008308,1594008309,1594008310,1598363176,1598363177,1640393707,1643511201,1645041136,1645041137,1645041138,1645041139,1645041140,1645041141,1645041142,1645041143,1645041144,1645041145,1645041146,1645041147,1645041148,1645041151,1645041152,1645041153,1645041154,1645041155,1645041156,1645041159,1645041160,1645041161,1645041162,1645041163,1645041164,1645041165,1645041166,1645041167,1645041168,1645041169,1645041170,1645041172,1645041173,1645041174,1645041175,1645041176,1645041177,1645041178,1645041179,1645041181,1645041182,1663211488,1663211489,1663211490,1663211491,1663211492,1663211493,1663211494,1663211495,1663211496,1663211497,1663211498,1663211499,1663211500,1663211501,1663211502,1663211503,1663211504,1663211505,1663211506,1663211507,1675194406,1675194407,1676492359,1676492360,1676492361,1676492362,1680386646,1720531225,1740804294,1783363172,1783363173,1812031554,1812031555,1826826303,1826826304,1858770643,1858770644,1872671701,1872671702,1887190212,1890165452,1890165453,1890165454,1892148255,1892148256,1892148257,1892148258,1892148259,1892148260,1892148261,1892148769,1892148770,1892148771,1892148772,1892148773,1892148774,1892148775,1892148776,1892148777,1892148778,1892148779,1892148780,1892148781,1892148782,1892148783,1892148784,1902187359,1902187386,1902616488,1902616490,1917827017,1920291587,1920760403,1920761068,1921264358,1921264359,1932950232,1932950233,1933916591,1934376566,1935765141,1953307251,1953307256,1954743538,1954743539,1954743540,1954743541,1957553466,1959443440,1959443441,1964174483,1964174484,1964630423,1964630425,1964630426,1964630997,1971807883,1971807884,1979411672,1979411673,1979411674,1979411675,1979411676,1979411677,1984139858,1985559124,1985559125,1986525592,1986525593,1986995995,1988405028,1988405029,1988405030,1991740483,1991740484,2026537021,2026537022,2026537023,2026537024,2026537025,2035967447,2035967448,2035967449,2035967450,2035967451,2037007664,2043946560,2043946561,2043946562,2050411221,2050411222,2050411223,2050411224,2050411225,2056852832,2056852833,2058623702,2058623703,2058623704,2058623705,2058623706,2058623707,2058623708,2058623709,2058623710,2060385861,2102001087,2102409153,2102409154,2102409155,2102409156,2104786162,2104786163,2104786164,2104786165,2104786166,2105176592,2112725307,2112725308,2112725309,2112725310,2127095678,2127095679,2127512193,2127512194,2127512195,2132910163,2132910164,2132910165,2132910166,2132910167,2132910168,2135864602,2144371391,2147326384,2147326385,2147550971,2147550972,2162663416,2162663417,2162663535,2163087820,2163087821,2163087822,2165256724,2165259212,2169937757,2175978487,2175978488,2175978489,2175978490,2177684430,2177684431,2180691285,2182844601,2182844602,2182844603,2198065208,2198065209,2198065210,2198065211,2198065212,2198065213,2198065266,2198065267,2198065268,2198065269,2198065270,2198065271,2198518194,2199748985,2200287855,2200287856,2200715907,2200715909,2200715910,2200715911,2204788904,2205216562,2205216563,2205216564,2205216565,2205216566,2206122513,2206122514,2206122515,2206122516,2206122517,2206122518,2206122519,2206122520,2206122521,2235617938,2243201627,2243201628,2243201629,2243201630,2255354629,2255354630,2255354631,2255354632,2255354633,2255354634,2267969064,2267969065,2267969066,2267969067,2269385060,2269385061,2270374236,2270374237,2294658893,2294658894,2298466923,2298466924,2298466925,2298466926,2310008053,2310008054,2310008055,2310008056,2310008057,2310008058,2310984721,2310984722,2310984723,2310984724,2310984725,2310984726,2315432960,2315432961,2315432962,2315432963,2316898093,2316898094,2356759039,2356759040,2359718761,2359718762,2359718763,2359718764,2359718765,2359718766,2359718767,2359718768,2359718769,2359718770,2359718771,2359718772,2373024640,2373024641,2373024642,2384403700,2384403701,2391884997,2391884998,2391884999,2391885000,2391885001,2391885002,2391885003,2391885004,2391885005,2391885006,2391885007,2391885008,2391885009,2391885010,2391885011,2391885012,2448028411,2448028412,2448028413,2449498061,2449498062,2449498063,2449498064,2449498065,2449498066,2454396506,2454396507,2454396508,2454396509,2454396510,2454396511,2456253366,2456253367,2456253368,2456253369,2456253370,2456253371,2468937486,2468937487,2468937488,2468937489,2468937490,2468937491,2472958981,2472958982,2472958983,2474856191,2474856192,2474856193,2475975931,2475975932,2475975933,2475975934,2475975935,2475975936,2475975937,2475975938,2475975939,2481528460,2481528461,2481528462,2481549883,2481549884,2487010227,2487010228,2487761039,2487761040,2490764562,2490764563,2490764564,2490764565,2490764566,2490764567,2507450090,2507450091,2507450092,2531783208,2531783209,2531783210,2531783320,2531783521,2531783522,2531783523,2531786048,2531786049,2531786050,2531789928,2531789929,2531789930,2531789931,2531789932,2531789933,2532396952,2532398313,2532398314,2532400125,2532400126,2532400127,2532400128,2532400129,2532400130,2532400131,2532400132,2532400133,2532400134,2532400135,2533012792,2533018467,2533018468,2533018469,2533018470,2533018471,2541736684,2541736685,2541736686,2541736687,2541736688,2541736689,2548285019,2550261875,2552134724,2552134725,2552134726,2552134727,2552134728,2567163695,2567163696,2567163697,2581557725,2582206382,2582206383,2582206384,2582206385,2582206386,2582206387,2582206388,2582206389,2582209602,2582860530,2582860531,2582860532,2582860533,2583512067,2583512069,2586774595,2586774596,2586774597,2597488663,2610203076,2610203077,2611023782,2611023783,2611023784,2611023785,2611057034,2611057038,2613135705,2613135706,2613135708,2613135709,2613135710,2613135711,2613135712,2613206294,2613206302,2613206304,2613991451,2613991452,2613991453,2614060456,2614060458,2614060460,2614163610,2614163611,2615628295,2615628296,2615628297,2615628298,2616184412,2616184413,2616184414,2616295983,2616529129,2618660866,2618660867,2618660868,2618660869,2618660870,2618660871,2618660872,2618660873,2618719800,2618719803,2618719808,2618719809,2619606073,2619606074,2619606075,2619606076,2619606077,2619661912,2619661913,2620477372,2620477373,2620477374,2620477375,2620477376,2620477377,2620482769,2620482770,2620482771,2620482772,2620482773,2620482774,2620512869,2620512870,2620512871,2620512872,2620512873,2620512874,2620512875,2620512876,2620512877,2620528887,2620528888,2620528889,2620528890,2620528891,2620528892,2620528893,2620528894,2620575888,2620575891,2620575896,2620575897,2620575898,2620575899,2620575901,2620575903,2620592440,2620592443,2622285547,2622285548,2622285549,2622285550,2622285551,2622285552,2622285553,2622841132,2622841133,2622841638,2623091162,2623265563,2623997794,2626809087,2626809088,2626809089,2626809090,2626809091,2626809092,2626809093,2626809094,2626809095,2626809096,2626809097,2626809852,2626809853,2626809854,2626809855,2626809856,2626809857,2626809858,2626809859,2626809860,2626809861,2626809862,2626809863,2626809864,2626809865,2626809866,2626809867,2626809868,2626809869,2626809870,2626809871,2626817256,2626817257,2626817258,2626817259,2626817260,2626821514,2626821515,2626821516,2626822242,2626822243,2626822244,2626822245,2626822246,2628129067,2628129068,2628129069,2628176975,2628176979,2628517177,2628517178,2628517179,2628517180,2628517181,2628517182,2628517183,2628517184,2628517185,2628517186,2628919209,2628919210,2632046425,2632046426,2632046427,2632046428,2632046429,2632046430,2632046431,2632046432,2632046433,2632046434,2632046435,2633259817,2633259818,2633683091,2633683092,2633683093,2633683094,2633683095,2633683096,2635009384,2635009385,2635009386,2635009387,2635009388,2635246049,2635469247,2635469248,2635469249,2635469250,2635469251,2635469252,2635469253,2635469254,2635469255,2635469256,2635469257,2635469258,2635469259,2635469260,2635469261,2635469262,2637259038,2637259039,2637259040,2637259041,2637259042,2637259043,2637259044,2637259045,2637259046,2637259047,2637259048,2637259049,2637259050,2637259051,2637259052,2637259053,2639006669,2645076641,2645076642,2645076643,2645076644,2645076645,2645076646,2645076647,2645076648,2645076649,2645076650,2645076651,2645076652,2645077475,2645077476,2645077477,2645077478,2645077479,2645077480,2645077481,2645077482,2645077483,2645077484,2645077485,2645077486,2645077487,2645077488,2645077489,2645077490,2645077491,2645077492,2645077493,2645077494,2645924368,2645924369,2645924370,2645924371,2645924372,2648763251,2648940224,2649071921,2649071922,2649071923,2649071924,2649071925,2649071926,2649071927,2649071928,2649071929,2649071930,2649071931,2649071932,2649071933,2649071934,2649071935,2649071936,2649071937,2649071938,2649071939,2649071940,2649071941,2649071942,2649071943,2649071944,2649405477,2649405480,2649405482,2649405483,2649405487,2649484217,2649484218,2649484219,2649484220,2649647156,2649647167,2649836199,2650290428,2650295803,2651923027,2651923031,2651923032,2651923033,2651923034,2653715413,2654556719,2654556720,2654556721,2654556722,2654556723,2654556724,2654556725,2654556727,2654556728,2654556729,2654556730,2654556731,2654556732,2654556733,2656068014,2656068015,2656068016,2656068017,2656068018,2656068019,2656068020,2656068021,2656068022,2656068023,2656068024,2656068025,2656068026,2656068027,2656068028,2656068029,2656068030,2656068031,2656068032,2656068033,2656068034,2656068035,2656068036,2656068037,2656068381,2656068382,2656068383,2656068384,2656068385,2656068386,2656069452,2656069453,2656069454,2656069455,2656069456,2656079206,2656079207,2656079208,2656079209,2656079210,2656079211,2656079212,2656079213,2656079214,2656079215,2656079216,2656079217,2656079218,2656079219,2656079220,2656079221,2656079222,2656079223,2656079224,2656079225,2656258411,2656403096,2656403097,2656403098,2656403099,2656403100,2656403101,2656403102,2656403103,2656403104,2656403105,2656403106,2656403107,2657765265,2657765266,2657765267,2657765268,2657765269,2657765270,2657765271,2672171992,2672171993,2672171994,2672171995,2672171996,2672171997,2672171998,2672171999,2672172000,2672172001,2672172002,2672172003,2672172004,2672172005,2672172006,2672172007,2672172008,2672172009,2672172010,2672172011,2672172012,2672172013,2672172014,2672172015,2682030687,2682030688,2682030689,2682030690,2682030691,2682030692,2682030693,2682030694,2682030695,2682030696,2682030697,2682030698,2682030699,2682030700,2682030701,2682030702,2687124990,2687124991,2687124992,2687124993,2687124994,2687124995,2690793075,2690793076,2690793077,2690793078,2690793079,2690793080,2690793081,2690793082,2690793083,2690793084,2690793085,2690793086,2698355288,2698355289,2698355290,2698355291,2698355292,2698355293,2698355294,2698355295,2698355296,2698355297,2698355298,2698355299,2698355300,2698355301,2698355302,2698355303,2698355304,2698355305,2698355306,2698355307,2698355308,2698355309,2698355310,2698355311,2698355312,2698355313,2698355314,2698355315,2698355316,2698355317,2698355318,2698355319,2698355320,2698355321,2698355322,2698355323,2698355324,2698355325,2698355326,2698355327,2706361690,2706375234,2706375235,2706375236,2706375237,2706401943,2706401945,2706401946,2706401947,2706401948,2706401949,2706401950,2707083549,2707908598,2707908599,2707908600,2707908601,2707908602,2707908603,2707908604,2707908605,2707908606,2707911637,2707911638,2707911639,2707911640,2707911641,2707911642,2707911643,2707911644,2707911645,2707911646,2707986509,2707989547,2707989548,2708404398,2708404399,2708404403,2708404404,2708404405,2708404408,2708443420,2708486589,2708486590,2708486591,2708486592,2708486593,2708486594,2708986349,2708986350,2708986351,2708986352,2708986353,2708986354,2708986355,2708986357,2708986358,2708986359,2708986360,2708986361,2708986362,2708986363,2708986364,2708986365,2708986366,2708986367,2708986368,2708986369,2708986370,2708986371,2708986372,2708986373,2708986374,2708986375,2708986376,2708986377,2708986378,2708986379,2708986380,2708986381,2708986382,2708986383,2708986384,2708986385,2708986386,2708986387,2708986388,2708986389,2708986390,2708986391,2708986392,2708986393,2708986394,2709025454,2709025458,2709252595,2709252596,2709252597,2709252598,2709252599,2709252600,2709252601,2709252602,2709581724,2709581725,2709581726,2709581727,2709873912);

Date: 2024-10-30 16:09:31
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.127.90

x Hide

WITH camps_with_created AS ( SELECT camp.id, camp.account_id, MAX(map.created) AS most_recent_map_created FROM clients_campaign camp JOIN api_data_ga_platformobject po ON po.gid = camp.gid JOIN api_data_ga_map map ON map.level_2_id = po.id WHERE camp.platform_id = 22 GROUP BY camp.id, camp.account_id ), untagged_most_recent_updated AS ( SELECT tag.tag_set_id, cache.updated AS latest_untagged FROM buckets_tagtoplatformcache cache JOIN buckets_tag tag ON cache.tag_id = tag.id AND tag.designation = 2 WHERE cache.platform_id = 22 ) SELECT DISTINCT ts_pl_rel.id AS tag_set_to_platform_level_id FROM buckets_tagsettoplatformlevel ts_pl_rel JOIN platforms_platformlevel pl ON pl.id = ts_pl_rel.platform_level_id AND pl.platform_id = 22 JOIN (( SELECT tag_set_id, most_recent_map_created FROM buckets_tagsettocampaign rel JOIN camps_with_created camp ON camp.id = rel.source_id) UNION ALL ( SELECT tag_set_id, most_recent_map_created FROM buckets_tagsettoaccount rel JOIN camps_with_created camp ON camp.account_id = rel.source_id) UNION ALL ( SELECT tagset.id AS tag_set_id, most_recent_map_created FROM buckets_tagset tagset JOIN vayner_portfolio port ON tagset.portfolio_id = port.id JOIN clients_account acc ON acc.portfolio_id = port.id JOIN camps_with_created camp ON camp.account_id = acc.id WHERE tagset.source_type = 4)) source_rel ON source_rel.tag_set_id = ts_pl_rel.tag_set_id LEFT JOIN untagged_most_recent_updated untagged ON untagged.tag_set_id = ts_pl_rel.tag_set_id WHERE (source_rel.most_recent_map_created >= untagged.latest_untagged OR untagged.latest_untagged IS NULL);

Date: 2024-10-30 08:53:48
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.177.81

x Hide

WITH camps_with_created AS ( SELECT camp.id, camp.account_id, MAX(map.created) AS most_recent_map_created FROM clients_campaign camp JOIN api_data_go_platformobject po ON po.gid = camp.gid JOIN api_data_go_map map ON map.level_3_id = po.id WHERE camp.platform_id = 17 GROUP BY camp.id, camp.account_id ), untagged_most_recent_updated AS ( SELECT tag.tag_set_id, cache.updated AS latest_untagged FROM buckets_tagtoplatformcache cache JOIN buckets_tag tag ON cache.tag_id = tag.id AND tag.designation = 2 WHERE cache.platform_id = 17 ) SELECT DISTINCT ts_pl_rel.id AS tag_set_to_platform_level_id FROM buckets_tagsettoplatformlevel ts_pl_rel JOIN platforms_platformlevel pl ON pl.id = ts_pl_rel.platform_level_id AND pl.platform_id = 17 JOIN (( SELECT tag_set_id, most_recent_map_created FROM buckets_tagsettocampaign rel JOIN camps_with_created camp ON camp.id = rel.source_id) UNION ALL ( SELECT tag_set_id, most_recent_map_created FROM buckets_tagsettoaccount rel JOIN camps_with_created camp ON camp.account_id = rel.source_id) UNION ALL ( SELECT tagset.id AS tag_set_id, most_recent_map_created FROM buckets_tagset tagset JOIN vayner_portfolio port ON tagset.portfolio_id = port.id JOIN clients_account acc ON acc.portfolio_id = port.id JOIN camps_with_created camp ON camp.account_id = acc.id WHERE tagset.source_type = 4)) source_rel ON source_rel.tag_set_id = ts_pl_rel.tag_set_id LEFT JOIN untagged_most_recent_updated untagged ON untagged.tag_set_id = ts_pl_rel.tag_set_id WHERE (source_rel.most_recent_map_created >= untagged.latest_untagged OR untagged.latest_untagged IS NULL);

Date: 2024-10-30 08:53:34
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.185.159

x Hide

SET LOCAL work_mem = '1024.00MB'; SELECT platform_object_id AS "level_1_id", CASE WHEN platform_breakdown_bucket_id IN (20895, 165, 28526, 28559, 24964, 23790, 23791, 23793, 23794, 23856, 23857, 28527, 20894, 20893, 20892, 20891, 15602, 15601, 15600, 15599, 15592, 15591, 15583, 15576, 15575, 15574, 15571, 15569, 15568, 15567, 15566, 15565, 15564, 15563, 18, 5338, 24684, 24686, 24685, 24688, 24689, 24687, 28528, 24827, 24828, 24829, 24830, 24831, 24832, 24893, 24895, 24896, 24897, 24901, 28529, 28560, 28578, 28579, 28580, 28650, 28581, 25846, 25847, 25848, 25849, 25850, 25851, 25997, 26908, 26909, 26910, 26911, 26941, 27887, 28033, 30538, 30804) THEN 20750 WHEN platform_breakdown_bucket_id IN (19, 163, 164, 24958, 20929, 24959, 24907, 24967, 24968, 24969, 24970, 24971, 24953, 24954, 24513, 24532, 24535, 24536, 24552, 24557, 25385, 25488, 23655, 23656, 23657, 23683, 21823, 23746, 21666, 21377, 20934, 20920, 20719, 20718, 20710, 20574, 20540, 15719, 15624, 15623, 15617, 15616, 15615, 15614, 15613, 15612, 15611, 15610, 15609, 15608, 15607, 15606, 4981, 15584, 15580, 15579, 15578, 15577, 15573, 15572, 15570, 12, 11, 9, 10, 8, 17, 5348, 5349, 25202, 25204, 25185, 24154, 24166, 24167, 24168, 22186, 22199, 24169, 24170, 24171, 25251, 22258, 22267, 24801, 25283, 24804, 24805, 24806, 24807, 24811, 24808, 24336, 24337, 24338, 24339, 24341, 24345, 24906, 24908, 24366, 24367, 24370, 24389, 28943, 29280, 26692, 26408, 26693, 26373, 26374, 26375, 26376, 26694, 26407, 27006, 27062, 29364, 29365, 30537, 30742, 30706, 30707, 31105, 31092, 31435, 31959) THEN 20749 WHEN platform_breakdown_bucket_id IN (16482, 15590, 15589, 15588, 15587, 15586, 15175, 22043, 22050, 22051, 22255, 22269) THEN 22014 WHEN platform_breakdown_bucket_id IN (20935, 20933, 17818, 15718, 15717, 15716, 15715, 15714, 15713, 15622, 15621, 15620, 15619, 15618, 15598, 15597, 15596, 15595, 15594, 15593, 15585, 15582, 15581, 15562, 15561, 15560, 15220, 22187, 8496, 8495, 20849) THEN 22013 ELSE 0 END AS "FB:fb_insta:PLATFORM_BREAKDOWN", SUM((data ->> 'spend')::numeric) AS "FB:spend:stats:1", SUM((data ->> 'impressions')::numeric) AS "FB:impressions:stats:1" FROM api_data_fb_stats_platformbreakdown_placement stats JOIN api_data_datasetfetchscratch_18 scratch ON scratch.object_id = stats.platform_object_id WHERE stats.date >= to_date('2017-09-14', 'YYYY-MM-DD') AND stats.date <= to_date('2024-10-30', 'YYYY-MM-DD') AND stats.platform_breakdown_bucket_id in ( SELECT bucket.id FROM platforms_platformbreakdownbucket bucket WHERE bucket.platform_breakdown_id = 3) GROUP BY "FB:fb_insta:PLATFORM_BREAKDOWN", "level_1_id";

Date: 2024-10-30 14:46:07
Duration: 0ms

x Hide

CREATE INDEX index_54382501 ON repack.table_48278051 USING btree (campaign_id);

Date: 2024-10-30 00:32:24
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

CREATE INDEX index_54382499 ON repack.table_48278051 USING btree (platform_breakdown_bucket_id);

Date: 2024-10-30 00:34:29
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

CREATE UNIQUE INDEX index_54970084 ON repack.table_48278051 USING btree (platform_object_id, platform_breakdown_bucket_id, date);

Date: 2024-10-30 00:31:40
Duration: 6m51s
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

CREATE UNIQUE INDEX index_54970084 ON repack.table_48278051 USING btree (platform_object_id, platform_breakdown_bucket_id, date);

Date: 2024-10-30 00:31:40
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

CREATE INDEX index_49613722 ON repack.table_48278058 USING btree (campaign_id);

Date: 2024-10-30 00:46:35
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

SELECT "api_data_gaplatformobjecttocustomplatforminfo"."ga_platform_object_id", "api_data_gaplatformobjecttocustomplatforminfo"."custom_platform_info_id" FROM "api_data_gaplatformobjecttocustomplatforminfo" INNER JOIN "api_data_gaplatformobjecttocustomplatforminfo_connection_rules" ON ("api_data_gaplatformobjecttocustomplatforminfo"."id" = "api_data_gaplatformobjecttocustomplatforminfo_connection_rules"."gaplatformobjecttocustomplatforminfo_id") WHERE "api_data_gaplatformobjecttocustomplatforminfo_connection_rules"."connectionrule_id" IN (154);

Date: 2024-10-30 09:22:02
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: u4uap6c6im3rso
Remote: 10.24.182.243

x Hide

CREATE INDEX index_49613721 ON repack.table_48278058 USING btree (platform_breakdown_bucket_id);

Date: 2024-10-30 00:45:48
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

CREATE INDEX index_49613723 ON repack.table_48278058 USING btree (platform_object_id);

Date: 2024-10-30 00:46:13
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

CREATE INDEX index_54382498 ON repack.table_48278051 USING btree (platform_object_id);

Date: 2024-10-30 00:36:27
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

CREATE UNIQUE INDEX index_67465906 ON repack.table_48278058 USING btree (id);

Date: 2024-10-30 00:45:19
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide

CREATE UNIQUE INDEX index_55178200 ON repack.table_48278051 USING btree (id);

Date: 2024-10-30 00:30:13
Duration: 5m23s
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

CREATE UNIQUE INDEX index_55178200 ON repack.table_48278051 USING btree (id);

Date: 2024-10-30 00:30:12
Duration: 0ms
Database: vayner_df3qcl88ea9t6e
User: root
Remote: 10.24.165.31

x Hide