Cohort Queries

gistfile1.txt

-- 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