8rG2NZgF

1DROP TABLE IF EXISTS schema1.experiment_dataset_v3;
2CREATE TABLE IF NOT EXISTS schema1.experiment_dataset_v3 AS
3        SELECT
4                bcs.*,
5            fbp.provider,
6        fbp.name,
7        fbp.country,
8        cspd.content_score AS content_score,
9
10    FROM schema1.first_batch_pups fbp
11    INNER JOIN schema2.statetransitions st ON (fbp.field_id = st.field_id)
12    INNER JOIN (
13        SELECT
14            field_id,
15            non_aggregated_field1,
16            non_aggregated_field2,
17            SUM(CASE
18                WHEN aggregated_field IS NULL THEN 0
19                ELSE aggregated_field
20             END) AS aggregated_field
21        FROM schema3.kpi_bcs bcs
22        WHERE bcs.yyyy_mm_dd >= '2019-04-21'
23        GROUP BY bcs.yyyy_mm_dd, field_id, non_aggregated_field1, non_aggregated_field_2
24    ) AS bcs ON bcs.field_id = fbp.field_id
25    INNER JOIN schema4.kpi_cspd cspd ON (fbp.field_id = cspd.field_id)
26    WHERE cspd.yyyy_mm_dd >= '2019-04-21' 
27    AND (st.cur_state_id IN (1,2,3));