-
-
Save savadev/0b02e22f49b21fbde9a08ae64e424faf to your computer and use it in GitHub Desktop.
PostgreSQL: joining two tables which are not related by a foreign key relationship
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment