Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Cohort Queries
-- invoices
SELECT
u.id AS user_id,
uuid_timestamp(i.id)::DATE AS dt,
sum(i.balance) AS inc_amt
FROM public.users u
JOIN public.invoices i ON u.id = i.user_id
WHERE
u.fraud_score != 'F'
AND uuid_timestamp(i.id) > DATE '2015-06-30'
AND uuid_timestamp(i.id) < DATE '2016-02-29'
AND i.balance > 0
GROUP BY u.id, dt
-- tries
select
u.id as user_id,
uuid_timestamp(t.id)::date as dt,
count(t.id) as inc_amt
from public.users u
join public.tries t on u.id = t.user_id
where u.fraud_score != 'F'
AND uuid_timestamp(t.id) >= DATE '2015-06-01'
AND uuid_timestamp(t.id) < DATE '2016-02-29'
and t.cancelled = false
group by u.id, dt
-- value of tries
select
u.id as user_id,
uuid_timestamp(t.id)::date as dt,
sum(t.price) + sum(t.sales_tax) as inc_amt
from public.users u
join public.tries t on u.id = t.user_id
where u.fraud_score != 'F'
AND uuid_timestamp(t.id) >= DATE '2015-06-01'
AND uuid_timestamp(t.id) < DATE '2016-02-29'
and t.cancelled = false
group by u.id, dt
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.