Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@ltenzil

This comment has been minimized.

Copy link

ltenzil commented Oct 1, 2018

Hi @drob, thanks for the post of lateral join, by any chance, you made any video?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.