FirstSubqueryID.sql

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
    ),