Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- rc.company_id,
- c.trading_name,
- SUM(rc.active) AS total_actives,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date)) as actives_m0,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '1' MONTH)) as actives_m1,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '2' MONTH)) as actives_m2,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '3' MONTH)) as actives_m3,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '4' MONTH)) as actives_m4,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '5' MONTH)) as actives_m5,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '6' MONTH)) as actives_m6,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '7' MONTH)) as actives_m7,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '8' MONTH)) as actives_m8,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '9' MONTH)) as actives_m9,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '10' MONTH)) as actives_m10,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '11' MONTH)) as actives_m11,
- SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '12' MONTH)) as actives_m12,
- FROM report.companies AS rc
- JOIN public.companies AS c ON rc.company_id = c.id
- GROUP BY rc.company_id, c.id
- ORDER BY rc.company_id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement