SecondSubqueryID.sql

action_view as
    (
        SELECT created_at::date AS viewdate,
            COUNT(av.userid) AS act_v,
            CASE when av.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN av.state ELSE 'other' END AS state,
            CASE WHEN av.current_medium IN ('source1', 'source2', 'source3') THEN av.current_medium ELSE 'other' END AS source,
            lower('id-' || av.created_at::date ||
                  '-' || CASE when av.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN av.state ELSE 'other' END ||
                  '-' || CASE WHEN av.current_medium IN ('source1', 'source2', 'source3') THEN av.current_medium ELSE 'other' END
            ) AS idp
        FROM act_view av
        WHERE created_at::date > getdate()::date - 60
        AND   created_at::date <= getdate()::date - 1
        GROUP BY viewdate, state, source
        ORDER BY viewdate, state, source
    )