Skip to content

Instantly share code, notes, and snippets.

Jonathan Hilgart jonhilgart22

  • San Francisco
View GitHub Profile
View reason text + problem types
-- 1) Get all problem types per patient
SELECT
date(p.created_at) as problem_created_date,
patient_id as patient_id,
CONCAT(CONCAT('[',GROUP_CONCAT(CONCAT('"', pt.lay_description), '"')), ']') as problem_names,
CONCAT(CONCAT('[',GROUP_CONCAT(p.problem_type_id, '')), ']') as problem_type_ids
from problems p
join (
View new pyenv virtualenv
pyenv virtualenv 2.7.10 my-virtual-env-2.7.10
Need to run - pyenv install 3.6.5 if the python version is not installed
View Demo versus no-demo query
with most_recent_subscription as (
select
user_id,
max(id) as subscription_id
from alooma.subscriptions
group by 1
),
weekly_minutes as (
select
a.user_id,
View Pricing plan for users on the waitlist
--waitlist
select
distinct
us.name,
us.email,
c.code as pricing_plan,
c.amount,
u.created_at::date as entered_credit_card_at
from user_events u
join coupons c
View VIP users
with most_recent_subscription as (
select
user_id,
max(created_at) as created_at
from subscriptions s
group by 1
),
weekly_minutes as (
select
date_trunc('month', subscription_started_at) as sub_start_month,
View Google Ads Tracking
select
a.subscription_created_at::date,
b.cleaned_source,
b.utm_campaign,
count(distinct b.browser_hash) as n_visits,
count(distinct case when w.first_user_created_event_at is not null then b.user_id else null end) as accounts_created,
count(distinct a.user_id) as credit_cards_added
from analytics.browser_hash_hits b
join analytics.acquisition_waterfall_by_browser_hash w
on w.browser_hash = b.browser_hash
View Invite Cold by Campaign Category last 7
select
a.subscription_invitee_credit_minutes,
mm.messager_campaign_category,
mm.messager_campaign_name,
count(distinct a.user_id) as n_users
from analytics.acquisition_credit_card_source_tracking a
join analytics.messager_message_results mm
on mm.user_id = a.user_id
-- email sent before card added
and mm.messager_message_created_at < a.credit_card_added_user_event_at
View lead scoring model data
-- TRAINING ---
with messager_message_and_webpage as (
select
mm.identity_id,
s.created_at as sub_start_date,
-- If the identity has subscribed, what emails did we send to them beforehand?
count(distinct case when mm.messager_message_created_at <= coalesce(s.created_at - '1 day'::interval, getdate() - '3 months'::interval) then mm.messager_message_id else null end) as number_of_emails_sent,
sum(case when mm.messager_message_created_at <= coalesce(s.created_at - '1 day'::interval, getdate() - '3 months'::interval) and mm.email_outbound_link_opened_at is not null then 1 else null end) as number_of_emails_clicked,
sum(case when mm.messager_message_created_at <= coalesce(s.created_at - '1 day'::interval, getdate() - '3 months'::interval) and mm.email_outbound_sendgrid_first_open_at is not null then 1 else null end) as number_of_emails_opened,
sum(case when mm.messager_message_created_at <= coalesce(s.created_at - '1 day'::interval, getdate() - '3 months'::interval) and mm.user_replied_to_m
You can’t perform that action at this time.