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

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

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:

drob

# How much space, in bytes, is used by the tables in this database?
psql -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public'" | sed -r "s/(.*)/\"SELECT * FROM pg_total_relation_size('\1')\"/g" | xargs -n 1 psql -c | egrep "\s+[0-9]+" | tr -d ' ' | awk '{total = total + $1}END{print total}'

# How much space, in bytes, is used by the indexes in this database?
psql -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public'" | sed -r "s/(.*)/\"SELECT * FROM pg_indexes_size('\1')\"/g" | xargs -n 1 psql -c | egrep "\s+[0-9]+" | tr -d ' ' | awk '{total = total + $1}END{print total}'

# This is not a proud gist.

drob

                                                         Table "public.app"
          Column           |          Type          |                                   Modifiers                                   
---------------------------+------------------------+-------------------------------------------------------------------------------
 app_id                    | bigint                 | not null default nextval('app_app_id_seq'::regclass)
 stripe_id                 | character varying(255) | 
 discount                  | character varying(255) | 
 free_trial_end            | bigint                 | not null default (date_part('epoch'::text, now()) * (1000)::double precision)
 name                      | character varying(255) | 
 email                     | character varying(255) | 
 time                      | bigint                 | default (date_part('epoch'::text, now()) * (1000)::double precision)

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

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