[postgresql] product.eppo_output.g_inc_experiment_cuped_lookback_data_frame_experiment_9018

Viewer

copydownloadembedprintName: product.eppo_output.g_inc_experiment_cuped_lookback_data_frame_experiment_9018
  1. CREATE TABLE product.eppo_output.g_inc_experiment_cuped_lookback_data_frame_experiment_9018 AS (
  2.   WITH all_entries AS (
  3.     SELECT
  4.       assigned_subject_std AS subject,
  5.       MIN(assigned_ts_std) AS entry_ts
  6.     FROM
  7.       product.eppo_output.g_inc_assignment_source__assn_839_experiment_9018
  8.     WHERE
  9.       assigned_ts_std BETWEEN '2023-07-22 00:00:00.000' :: timestamp
  10.       AND '2023-09-19 00:00:00.000' :: timestamp
  11.     GROUP BY
  12.       1
  13.   ),
  14.   mes_facts_1554 AS (
  15.     SELECT
  16.       USER_ID :: VARCHAR AS subject,
  17.       DATE :: timestamp AS event_ts,
  18.       SITEWIDE_MW :: FLOAT8 AS fact_4542,
  19.       BROWSE_MW :: FLOAT8 AS fact_4921,
  20.       PLATFORM AS fact_dim_14
  21.     FROM
  22.       (
  23.         SELECT
  24.           date,
  25.           device_id,
  26.           user_id,
  27.           platform,
  28.           sitewide_vp,
  29.           sitewide_mw,
  30.           sitewide_fmp,
  31.           leftnav_vp,
  32.           leftnav_mw,
  33.           leftnav_fmp,
  34.           discover_vp,
  35.           discover_mw,
  36.           discover_fmp,
  37.           offline_channel_vp,
  38.           offline_channel_mw,
  39.           offline_channel_fmp,
  40.           browse_vp,
  41.           browse_fmp,
  42.           browse_mw,
  43.           search_vp,
  44.           search_fmp,
  45.           search_mw,
  46.           recs_vp,
  47.           recs_mw,
  48.           recs_fmp,
  49.           search_starts,
  50.           search_sessions_converted_to_vp,
  51.           search_sessions_converted_to_fmp,
  52.           following_vp,
  53.           following_mw,
  54.           following_fmp
  55.         FROM
  56.           cubes.daily_viewer_metrics_by_surface
  57.       )
  58.     WHERE
  59.       DATE(DATE) >= DATE('2023-08-22')
  60.       AND DATE(DATE) < DATE('2023-09-20')
  61.       AND DATE :: timestamp >= '2023-08-22 00:00:00.000' :: timestamp
  62.       AND DATE :: timestamp < '2023-09-20 00:00:00.000' :: timestamp
  63.   ),
  64.   mes_aggs_1554 AS (
  65.     SELECT
  66.       subject,
  67.       SUM(
  68.         CASE
  69.           WHEN fact_dim_14 :: VARCHAR IN (text 'ios') THEN fact_4542
  70.           ELSE 0
  71.         END
  72.       ) AS aggregation_11174,
  73.       SUM(
  74.         CASE
  75.           WHEN fact_dim_14 :: VARCHAR IN (text 'ios') THEN fact_4921
  76.           ELSE 0
  77.         END
  78.       ) AS aggregation_11191
  79.     FROM
  80.       (
  81.         SELECT
  82.           e.subject,
  83.           e.entry_ts,
  84.           f.event_ts,
  85.           f.fact_4542,
  86.           f.fact_4921,
  87.           f.fact_dim_14
  88.         FROM
  89.           mes_facts_1554 f
  90.           INNER JOIN all_entries e ON f.subject = e.subject
  91.         WHERE
  92.           f.event_ts BETWEEN DATEADD(day, -31, e.entry_ts)
  93.           AND DATEADD(day, -1, e.entry_ts)
  94.       )
  95.     GROUP BY
  96.       1
  97.   ),
  98.   mes_facts_2759 AS (
  99.     SELECT
  100.       USER_ID :: VARCHAR AS subject,
  101.       DATE :: timestamp AS event_ts,
  102.       CLIPS_MW :: FLOAT8 AS fact_7400,
  103.       PLATFORM AS fact_dim_744
  104.     FROM
  105.       (
  106.         select
  107.           date,
  108.           user_id,
  109.           device_id,
  110.           platform,
  111.           count(*) as clips_mw
  112.         from
  113.           spade.clips_minute_watched
  114.         where
  115.           date >= '2023-06-01'
  116.         group by
  117.           1,
  118.           2,
  119.           3,
  120.           4
  121.       )
  122.     WHERE
  123.       DATE(DATE) >= DATE('2023-08-22')
  124.       AND DATE(DATE) < DATE('2023-09-20')
  125.       AND DATE :: timestamp >= '2023-08-22 00:00:00.000' :: timestamp
  126.       AND DATE :: timestamp < '2023-09-20 00:00:00.000' :: timestamp
  127.   ),
  128.   mes_aggs_2759 AS (
  129.     SELECT
  130.       subject,
  131.       SUM(
  132.         CASE
  133.           WHEN fact_dim_744 :: VARCHAR IN (text 'ios') THEN fact_7400
  134.           ELSE 0
  135.         END
  136.       ) AS aggregation_11202
  137.     FROM
  138.       (
  139.         SELECT
  140.           e.subject,
  141.           e.entry_ts,
  142.           f.event_ts,
  143.           f.fact_7400,
  144.           f.fact_dim_744
  145.         FROM
  146.           mes_facts_2759 f
  147.           INNER JOIN all_entries e ON f.subject = e.subject
  148.         WHERE
  149.           f.event_ts BETWEEN DATEADD(day, -31, e.entry_ts)
  150.           AND DATEADD(day, -1, e.entry_ts)
  151.       )
  152.     GROUP BY
  153.       1
  154.   )
  155.   SELECT
  156.     ae.subject,
  157.     mes_aggs_1554.aggregation_11174 AS aggregation_11174,
  158.     mes_aggs_1554.aggregation_11191 AS aggregation_11191,
  159.     mes_aggs_2759.aggregation_11202 AS aggregation_11202
  160.   FROM
  161.     all_entries ae
  162.     LEFT JOIN mes_aggs_1554 ON ae.subject = mes_aggs_1554.subject
  163.     LEFT JOIN mes_aggs_2759 ON ae.subject = mes_aggs_2759.subject
  164. )

Editor

You can edit this paste and save as new:


File Description
  • product.eppo_output.g_inc_experiment_cuped_lookback_data_frame_experiment_9018
  • Paste Code
  • 03 Oct-2023
  • 4.25 Kb
You can Share it: