WITH actions AS
    (
        SELECT  ca.created_at::date as actdate,
                COUNT(*) AS act,
                CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END AS state,
                CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END AS source,
                lower('id-' || ca.created_at::date ||
                      '-' || CASE when ca.state IN ('CA', 'WA', 'DC', 'NY', 'IL') THEN ca.state ELSE 'other' END ||
                      '-' || CASE WHEN ca.current_medium IN ('source1', 'source2', 'source3') THEN ca.current_medium ELSE 'other' END
                ) AS id
        FROM  events e
        JOIN  create_action ca ON ca.action_id = e.id
        WHERE   e.type > 0
        AND   ca.created_at::date >  getdate()::date - 60
        AND   ca.created_at::date <= getdate()::date - 1
        GROUP BY actdate, state, source
        ORDER BY actdate, state, source
    ),


     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
    )


    SELECT av.sapdate             AS date,
           lower(av.state)        AS state,
           lower(av.source)       AS source,
           av.act_v               AS act_view,
           a.act::int             AS act,
    FROM action_view av
    LEFT OUTER JOIN actions a ON av.idp = a.id
    ORDER BY date;