Last active
March 3, 2020 08:26
-
-
Save drob/3abc0b917cc8a941f39e to your computer and use it in GitHub Desktop.
This file contains 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
SELECT | |
sum(view_homepage) AS viewed_homepage, | |
sum(enter_credit_card) AS entered_credit_card | |
FROM ( | |
-- Get the first time each user viewed the homepage. | |
SELECT | |
user_id, | |
1 AS view_homepage, | |
min(time) AS view_homepage_time | |
FROM event | |
WHERE | |
data->>'type' = 'view_homepage' | |
GROUP BY user_id | |
) e1 LEFT JOIN LATERAL ( | |
-- For each (user_id, view_homepage_time) tuple, get the first time that | |
-- user did the enter_credit_card event, if one exists within two weeks. | |
SELECT | |
1 AS enter_credit_card, | |
time AS enter_credit_card_time | |
FROM event | |
WHERE | |
user_id = e1.user_id AND | |
data->>'type' = 'enter_credit_card' AND | |
time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14) | |
ORDER BY time | |
LIMIT 1 | |
) e2 ON true |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi @drob, thanks for the post of lateral join, by any chance, you made any video?