drob

CREATE TABLE event (
  user_id BIGINT,
  event_id BIGINT,
  time BIGINT NOT NULL,
  data JSON NOT NULL,
  PRIMARY KEY (user_id, event_id)
)

drob

SELECT 
  user_id,
  view_homepage,
  view_homepage_time,
  enter_credit_card,
  enter_credit_card_time
FROM (
  -- Get the first time each user viewed the homepage.
  SELECT
    user_id,

drob

SELECT
  user_id,
  1 AS view_homepage,
  min(time) AS view_homepage_time
FROM event
WHERE
  data->>'type' = 'view_homepage'
GROUP BY user_id

drob

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

drob

 user_id | view_homepage | view_homepage_time | enter_credit_card | enter_credit_card_time 
---------+---------------+--------------------+-------------------+------------------------
     567 |             1 |         5234567890 |                 1 |             5839367890
     234 |             1 |         2234567890 |                   |                       
     345 |             1 |         3234567890 |                   |                       
     456 |             1 |         4234567890 |                   |                       
     678 |             1 |         6234567890 |                   |                       
     123 |             1 |         1234567890 |                   |                       
 ...

drob

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

drob

 viewed_homepage | entered_credit_card 
-----------------+---------------------
             827 |                 103

drob

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

drob

 viewed_homepage | use_demo | entered_credit_card 
-----------------+----------+---------------------
             827 |      220 |                  86

drob

Keybase proof

I hereby claim:

• I am drob on github.
• I am drob (https://keybase.io/drob) on keybase.
• I have a public key whose fingerprint is 4E5F B7FB 241F 3D85 0821 1524 164D A672 96E9 A956

To claim this, I am signing this object: