left_join.sql

explain

select 
  count(t1.user_id) as viewed_homepage,
  count(distinct t2.user_id) as used_demo,
  count(distinct t3.user_id) as entered_credit_card

from (
  select
    user_id,
    min(time) as view_homepage_time
  from event
  where
    data->>'type' = 'view_homepage'
  group by user_id
) t1 

left join (
  select
    user_id,
    time as use_demo_time
  from event
  where data->>'type' = 'use_demo'
) t2 on t1.user_id = t2.user_id
    and t2.use_demo_time between t1.view_homepage_time 
                             and (t1.view_homepage_time + 1000*60*60*24*7)

left join (
  select
    user_id,
    time as enter_credit_card_time
  from event
  where data->>'type' = 'enter_credit_card'
) t3 on t2.user_id = t3.user_id 
    and t3.enter_credit_card_time between t2.use_demo_time 
                                      and (t2.use_demo_time + 1000*60*60*24*7)

/*
Aggregate  (cost=58.53..58.54 rows=1 width=24)
  ->  Nested Loop Left Join  (cost=27.30..58.51 rows=2 width=24)
        ->  Nested Loop Left Join  (cost=26.31..45.63 rows=2 width=24)
              ->  HashAggregate  (cost=24.12..24.14 rows=2 width=16)
                    ->  Seq Scan on event event_2  (cost=0.00..24.10 rows=5 width=16)
                          Filter: ((data ->> 'type'::text) = 'view_homepage'::text)
              ->  Bitmap Heap Scan on event  (cost=2.19..10.72 rows=1 width=16)
                    Recheck Cond: (event_2.user_id = user_id)
                    Filter: (("time" >= (min(event_2."time"))) AND ((data ->> 'type'::text) = 'use_demo'::text) AND ("time" <= ((min(event_2."time")) + 604800000)))
                    ->  Bitmap Index Scan on event_pkey  (cost=0.00..2.19 rows=5 width=0)
                          Index Cond: (event_2.user_id = user_id)
        ->  Bitmap Heap Scan on event event_1  (cost=0.99..6.43 rows=1 width=16)
              Recheck Cond: (event.user_id = user_id)
              Filter: (("time" >= event."time") AND ((data ->> 'type'::text) = 'enter_credit_card'::text) AND ("time" <= (event."time" + 604800000)))
              ->  Bitmap Index Scan on event_pkey  (cost=0.00..0.99 rows=5 width=0)
                    Index Cond: (event.user_id = user_id)
*/

left_join_cte.sql

explain

with

t1 as (
  select
    user_id,
    min(time) as view_homepage_time
  from event
  where
    data->>'type' = 'view_homepage'
  group by user_id
),

t2 as (  
  select
    t1.user_id as t1_user_id,
    e.user_id,
    min(e.time) as use_demo_time
  from t1
  left join event e on t1.user_id = e.user_id
                   and e.time between t1.view_homepage_time 
                                  and (t1.view_homepage_time + 1000*60*60*24*7)
  where e.data->>'type' = 'use_demo'
  group by 
    t1.user_id,
    e.user_id
),

t3 as (
  select
    t2.t1_user_id,
    t2.user_id as t2_user_id,
    e.user_id,
    min(e.time) as enter_credit_card_time
  from t2
  left join event e on t2.user_id = e.user_id 
                   and e.time between t2.use_demo_time 
                                  and (t2.use_demo_time + 1000*60*60*24*7)
  where data->>'type' = 'enter_credit_card'
  group by
    t2.t1_user_id,
    t2.user_id,
    e.user_id
)

select
  count(t3.t1_user_id) as viewed_homepage,
  count(t3.t2_user_id) as used_demo,
  count(t3.user_id) as entered_credit_card
from t3

/*
Aggregate  (cost=58.47..58.48 rows=1 width=24)
  CTE t1
    ->  HashAggregate  (cost=24.12..24.14 rows=2 width=16)
          ->  Seq Scan on event  (cost=0.00..24.10 rows=5 width=16)
                Filter: ((data ->> 'type'::text) = 'view_homepage'::text)
  CTE t2
    ->  HashAggregate  (cost=21.51..21.52 rows=1 width=24)
          ->  Nested Loop  (cost=2.19..21.51 rows=1 width=24)
                ->  CTE Scan on t1  (cost=0.00..0.04 rows=2 width=16)
                ->  Bitmap Heap Scan on event e  (cost=2.19..10.72 rows=1 width=16)
                      Recheck Cond: (user_id = t1.user_id)
                      Filter: (("time" >= t1.view_homepage_time) AND ((data ->> 'type'::text) = 'use_demo'::text) AND ("time" <= (t1.view_homepage_time + 604800000)))
                      ->  Bitmap Index Scan on event_pkey  (cost=0.00..2.19 rows=5 width=0)
                            Index Cond: (user_id = t1.user_id)
  CTE t3
    ->  HashAggregate  (cost=12.76..12.77 rows=1 width=32)
          ->  Nested Loop  (cost=4.19..12.75 rows=1 width=32)
                ->  CTE Scan on t2  (cost=0.00..0.02 rows=1 width=24)
                ->  Bitmap Heap Scan on event e_1  (cost=4.19..12.72 rows=1 width=16)
                      Recheck Cond: (user_id = t2.user_id)
                      Filter: (("time" >= t2.use_demo_time) AND ((data ->> 'type'::text) = 'enter_credit_card'::text) AND ("time" <= (t2.use_demo_time + 604800000)))
                      ->  Bitmap Index Scan on event_pkey  (cost=0.00..4.19 rows=5 width=0)
                            Index Cond: (user_id = t2.user_id)
  ->  CTE Scan on t3  (cost=0.00..0.02 rows=1 width=24)
*/

left_join_lateral.sql

explain

SELECT 
  count(e1.user_id) AS viewed_homepage,
  count(distinct e2.user_id)  AS use_demo,
  count(distinct e3.user_id) AS entered_credit_card
FROM (
  -- Get the first time each user viewed the homepage.
  SELECT
    user_id,
    min(time) AS view_homepage_time
  FROM event
  WHERE
    data->>'type' = 'view_homepage'
  GROUP BY user_id
) e1 LEFT JOIN LATERAL (
  -- For each row, get the first time the user_id did the use_demo
  -- event, if one exists within one week of view_homepage_time.
  SELECT
    user_id,
    time AS use_demo_time
  FROM event
  WHERE
    user_id = e1.user_id AND
    data->>'type' = 'use_demo' AND
    time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*7)
) e2 ON true LEFT JOIN LATERAL (
  -- For each row, get the first time the user_id did the enter_credit_card
  -- event, if one exists within one week of use_demo_time.
  SELECT
    user_id,
    time AS enter_credit_card_time
  FROM event
  WHERE
    user_id = e2.user_id AND
    data->>'type' = 'enter_credit_card' AND
    time BETWEEN use_demo_time AND (use_demo_time + 1000*60*60*24*7)
) e3 ON true

/*
Aggregate  (cost=58.53..58.54 rows=1 width=24)
  ->  Nested Loop Left Join  (cost=27.30..58.51 rows=2 width=24)
        ->  Nested Loop Left Join  (cost=26.31..45.63 rows=2 width=24)
              ->  HashAggregate  (cost=24.12..24.14 rows=2 width=16)
                    ->  Seq Scan on event event_2  (cost=0.00..24.10 rows=5 width=16)
                          Filter: ((data ->> 'type'::text) = 'view_homepage'::text)
              ->  Bitmap Heap Scan on event  (cost=2.19..10.72 rows=1 width=16)
                    Recheck Cond: (user_id = event_2.user_id)
                    Filter: (("time" >= (min(event_2."time"))) AND ((data ->> 'type'::text) = 'use_demo'::text) AND ("time" <= ((min(event_2."time")) + 604800000)))
                    ->  Bitmap Index Scan on event_pkey  (cost=0.00..2.19 rows=5 width=0)
                          Index Cond: (user_id = event_2.user_id)
        ->  Bitmap Heap Scan on event event_1  (cost=0.99..6.43 rows=1 width=16)
              Recheck Cond: (user_id = event.user_id)
              Filter: (("time" >= event."time") AND ((data ->> 'type'::text) = 'enter_credit_card'::text) AND ("time" <= (event."time" + 604800000)))
              ->  Bitmap Index Scan on event_pkey  (cost=0.00..0.99 rows=5 width=0)
                    Index Cond: (user_id = event.user_id)
*/

left_join_lateral_order_by_limit.sql

explain

SELECT 
  sum(view_homepage) AS viewed_homepage,
  sum(use_demo) AS use_demo,
  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 row, get the first time the user_id did the use_demo
  -- event, if one exists within one week of view_homepage_time.
  SELECT
    user_id,
    1 AS use_demo,
    time AS use_demo_time
  FROM event
  WHERE
    user_id = e1.user_id AND
    data->>'type' = 'use_demo' AND
    time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*7)
  ORDER BY time
  LIMIT 1
) e2 ON true LEFT JOIN LATERAL (
  -- For each row, get the first time the user_id did the enter_credit_card
  -- event, if one exists within one week of use_demo_time.
  SELECT
    1 AS enter_credit_card,
    time AS enter_credit_card_time
  FROM event
  WHERE
    user_id = e2.user_id AND
    data->>'type' = 'enter_credit_card' AND
    time BETWEEN use_demo_time AND (use_demo_time + 1000*60*60*24*7)
  ORDER BY time
  LIMIT 1
) e3 ON true

/*
Aggregate  (cost=75.21..75.22 rows=1 width=12)
  ->  Nested Loop Left Join  (cost=49.59..75.20 rows=2 width=12)
        ->  Nested Loop Left Join  (cost=36.86..49.68 rows=2 width=24)
              ->  HashAggregate  (cost=24.12..24.14 rows=2 width=16)
                    ->  Seq Scan on event  (cost=0.00..24.10 rows=5 width=16)
                          Filter: ((data ->> 'type'::text) = 'view_homepage'::text)
              ->  Limit  (cost=12.73..12.74 rows=1 width=16)
                    ->  Sort  (cost=12.73..12.74 rows=1 width=16)
                          Sort Key: event_1."time"
                          ->  Bitmap Heap Scan on event event_1  (cost=4.19..12.72 rows=1 width=16)
                                Recheck Cond: (user_id = event.user_id)
                                Filter: (("time" >= (min(event."time"))) AND ("time" <= ((min(event."time")) + 604800000)) AND ((data ->> 'type'::text) = 'use_demo'::text))
                                ->  Bitmap Index Scan on event_pkey  (cost=0.00..4.19 rows=5 width=0)
                                      Index Cond: (user_id = event.user_id)
        ->  Limit  (cost=12.73..12.74 rows=1 width=8)
              ->  Sort  (cost=12.73..12.74 rows=1 width=8)
                    Sort Key: event_2."time"
                    ->  Bitmap Heap Scan on event event_2  (cost=4.19..12.72 rows=1 width=8)
                          Recheck Cond: (user_id = event_1.user_id)
                          Filter: (("time" >= event_1."time") AND ("time" <= (event_1."time" + 604800000)) AND ((data ->> 'type'::text) = 'enter_credit_card'::text))
                          ->  Bitmap Index Scan on event_pkey  (cost=0.00..4.19 rows=5 width=0)
                                Index Cond: (user_id = event_1.user_id)
*/