Skip to content

Instantly share code, notes, and snippets.

Jonathan Hilgart jonhilgart22

  • San Francisco
Block or report user

Report or block jonhilgart22

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View roam todo reassignment query
select
r.todo_id,
extract(day from r.created_at) as prediction_day,
ptc.`text`,
group_concat(r.annotation_label, ' | ') as annotation_labels
from roam_message_label_predictions r
join patient_timeline_post_text_contents ptc
on ptc.post_id = r.patient_timeline_post_id
View number of todos from messages reassigned per queue
-- For TODOS that come in from messages, how many times is a TODO reassigned?
-- Assume last role_id per todo is the correct role
-- This all includes Matchmaker rerouting logic
-- NB: Admin may have a minimum of two roles (2,12)
SELECT
d.role_name as last_role_per_todo,
-- zero means there was ONE assignment
count(distinct case when tv.total_role_ids_per_todo = 1 then tv.virtual_item_id else null end) as one_assignment,
count(distinct case when tv.total_role_ids_per_todo = 2 then tv.virtual_item_id else null end) as two_assignments,
count(distinct case when tv.total_role_ids_per_todo between 3 and 5 then tv.virtual_item_id else null end) as three_to_five_assignments,
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
You can’t perform that action at this time.