[postgresql] product.eppo_output.g_inc_experiment_cuped_lookback_data_frame_experiment_9018
Viewer
*** This page was generated with the meta tag "noindex, nofollow". This happened because you selected this option before saving or the system detected it as spam. This means that this page will never get into the search engines and the search bot will not crawl it. There is nothing to worry about, you can still share it with anyone.
- CREATE TABLE product.eppo_output.g_inc_experiment_cuped_lookback_data_frame_experiment_9018 AS (
- WITH all_entries AS (
- SELECT
- assigned_subject_std AS subject,
- MIN(assigned_ts_std) AS entry_ts
- FROM
- product.eppo_output.g_inc_assignment_source__assn_839_experiment_9018
- WHERE
- assigned_ts_std BETWEEN '2023-07-22 00:00:00.000' :: timestamp
- AND '2023-09-19 00:00:00.000' :: timestamp
- GROUP BY
- 1
- ),
- mes_facts_1554 AS (
- SELECT
- USER_ID :: VARCHAR AS subject,
- DATE :: timestamp AS event_ts,
- SITEWIDE_MW :: FLOAT8 AS fact_4542,
- BROWSE_MW :: FLOAT8 AS fact_4921,
- PLATFORM AS fact_dim_14
- FROM
- (
- SELECT
- date,
- device_id,
- user_id,
- platform,
- sitewide_vp,
- sitewide_mw,
- sitewide_fmp,
- leftnav_vp,
- leftnav_mw,
- leftnav_fmp,
- discover_vp,
- discover_mw,
- discover_fmp,
- offline_channel_vp,
- offline_channel_mw,
- offline_channel_fmp,
- browse_vp,
- browse_fmp,
- browse_mw,
- search_vp,
- search_fmp,
- search_mw,
- recs_vp,
- recs_mw,
- recs_fmp,
- search_starts,
- search_sessions_converted_to_vp,
- search_sessions_converted_to_fmp,
- following_vp,
- following_mw,
- following_fmp
- FROM
- cubes.daily_viewer_metrics_by_surface
- )
- WHERE
- DATE(DATE) >= DATE('2023-08-22')
- AND DATE(DATE) < DATE('2023-09-20')
- AND DATE :: timestamp >= '2023-08-22 00:00:00.000' :: timestamp
- AND DATE :: timestamp < '2023-09-20 00:00:00.000' :: timestamp
- ),
- mes_aggs_1554 AS (
- SELECT
- subject,
- SUM(
- CASE
- WHEN fact_dim_14 :: VARCHAR IN (text 'ios') THEN fact_4542
- ELSE 0
- END
- ) AS aggregation_11174,
- SUM(
- CASE
- WHEN fact_dim_14 :: VARCHAR IN (text 'ios') THEN fact_4921
- ELSE 0
- END
- ) AS aggregation_11191
- FROM
- (
- SELECT
- e.subject,
- e.entry_ts,
- f.event_ts,
- f.fact_4542,
- f.fact_4921,
- f.fact_dim_14
- FROM
- mes_facts_1554 f
- INNER JOIN all_entries e ON f.subject = e.subject
- WHERE
- f.event_ts BETWEEN DATEADD(day, -31, e.entry_ts)
- AND DATEADD(day, -1, e.entry_ts)
- )
- GROUP BY
- 1
- ),
- mes_facts_2759 AS (
- SELECT
- USER_ID :: VARCHAR AS subject,
- DATE :: timestamp AS event_ts,
- CLIPS_MW :: FLOAT8 AS fact_7400,
- PLATFORM AS fact_dim_744
- FROM
- (
- select
- date,
- user_id,
- device_id,
- platform,
- count(*) as clips_mw
- from
- spade.clips_minute_watched
- where
- date >= '2023-06-01'
- group by
- 1,
- 2,
- 3,
- 4
- )
- WHERE
- DATE(DATE) >= DATE('2023-08-22')
- AND DATE(DATE) < DATE('2023-09-20')
- AND DATE :: timestamp >= '2023-08-22 00:00:00.000' :: timestamp
- AND DATE :: timestamp < '2023-09-20 00:00:00.000' :: timestamp
- ),
- mes_aggs_2759 AS (
- SELECT
- subject,
- SUM(
- CASE
- WHEN fact_dim_744 :: VARCHAR IN (text 'ios') THEN fact_7400
- ELSE 0
- END
- ) AS aggregation_11202
- FROM
- (
- SELECT
- e.subject,
- e.entry_ts,
- f.event_ts,
- f.fact_7400,
- f.fact_dim_744
- FROM
- mes_facts_2759 f
- INNER JOIN all_entries e ON f.subject = e.subject
- WHERE
- f.event_ts BETWEEN DATEADD(day, -31, e.entry_ts)
- AND DATEADD(day, -1, e.entry_ts)
- )
- GROUP BY
- 1
- )
- SELECT
- ae.subject,
- mes_aggs_1554.aggregation_11174 AS aggregation_11174,
- mes_aggs_1554.aggregation_11191 AS aggregation_11191,
- mes_aggs_2759.aggregation_11202 AS aggregation_11202
- FROM
- all_entries ae
- LEFT JOIN mes_aggs_1554 ON ae.subject = mes_aggs_1554.subject
- LEFT JOIN mes_aggs_2759 ON ae.subject = mes_aggs_2759.subject
- )
Editor
You can edit this paste and save as new: