Skip to content

Instantly share code, notes, and snippets.

Avatar

Dan Robinson drob

View GitHub Profile
View app_schema.txt
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)
View keybase.md

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:

View lateral_join_funnel_2_sum.log
viewed_homepage | use_demo | entered_credit_card
-----------------+----------+---------------------
827 | 220 | 86
View lateral_join_funnel_2.sql
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
View lateral_join_funnel_1_sum.log
viewed_homepage | entered_credit_card
-----------------+---------------------
827 | 103
View lateral_join_funnel_1_3.sql
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
View lateral_join_funnel_1_result.log
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 | |
...
View lateral_join_funnel_1_2.sql
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
View lateral_join_funnel_1_1.sql
SELECT
user_id,
1 AS view_homepage,
min(time) AS view_homepage_time
FROM event
WHERE
data->>'type' = 'view_homepage'
GROUP BY user_id
View lateral_join_funnel_1.sql
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,