Skip to content

Instantly share code, notes, and snippets.

@arush
Last active March 9, 2016 06:48
Show Gist options
  • Save arush/8e4592ccba4d42522200 to your computer and use it in GitHub Desktop.
Save arush/8e4592ccba4d42522200 to your computer and use it in GitHub Desktop.
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