SELECT "t2"."lt", coalesce(t2.deposits_total, 0) AS "deposits_total", coalesce(t2.players_total, 0) AS "players_total", coalesce(t2.registrations_count, 0) AS "registrations_count", "t2"."in_out_total", "t1"."registration_month", "t1"."deposit_month", "t1"."players_count", "t1"."deposits_count", "t1"."deposits_sum" FROM (SELECT "registration_month", array_to_json(array_agg(t1.deposit_month ORDER BY deposit_month)) AS deposit_month, array_to_json(array_agg(t1.players_count ORDER BY deposit_month)) AS players_count, array_to_json(array_agg(t1.deposits_count ORDER BY deposit_month)) AS deposits_count, array_to_json(array_agg(t1.deposits_sum ORDER BY deposit_month)) AS deposits_sum FROM (WITH "stats" AS (SELECT date_trunc('month', p.created_at) AS registration_month, date_trunc('month', d.created_at) AS deposit_month, COUNT(DISTINCT d.player_id) AS players_count, SUM(d.amount) AS deposits_sum, SUM(1) AS deposits_count FROM "deposits" AS "d" INNER JOIN "players" AS "p" ON ("p"."id" = "d"."player_id") INNER JOIN "player_stats" AS "ps" ON ("p"."id" = "ps"."player_id") WHERE ((p.created_at::date BETWEEN '2014-06-01 00:00:00 UTC'::date AND '2016-06-30 23:59:59 UTC'::date) AND ("d"."amount" > 0)) GROUP BY date_trunc('month', d.created_at), date_trunc('month', p.created_at)), "reg_months" AS (SELECT generate_series('2014-06-01 00:00:00 UTC'::date, '2016-06-30 23:59:59 UTC'::date, '1 month') AS MONTH), "dep_months" AS (SELECT generate_series('2014-06-01 00:00:00 UTC'::date, '2016-06-30 23:59:59 UTC'::date, '1 month') AS MONTH) SELECT "reg_months"."month" AS "registration_month", "dep_months"."month" AS "deposit_month", coalesce(stats.players_count, 0) AS "players_count", coalesce(stats.deposits_count, 0) AS "deposits_count", coalesce(stats.deposits_sum, 0) AS "deposits_sum" FROM "reg_months" CROSS JOIN "dep_months" LEFT JOIN "stats" ON (("reg_months"."month" = "stats"."registration_month") AND ("dep_months"."month" = "stats"."deposit_month")) WHERE ("reg_months"."month" <= "dep_months"."month")) AS "t1" GROUP BY "registration_month") AS "t1" LEFT JOIN (SELECT AVG(extract(epoch FROM age(last_deposit_created_at, first_deposit_created_at)) / extract(epoch FROM interval '1 day')) AS lt, SUM(deposits_sum) AS deposits_total, SUM(deposits_sum - withdraws_sum) AS in_out_total, date_trunc('month', p.created_at) AS registration_month, COUNT(*) AS registrations_count, SUM(CASE WHEN deposits_sum > 0 THEN 1 ELSE 0 END) AS players_total FROM "players" AS "p" LEFT JOIN "player_stats" AS "ps" ON ("p"."id" = "ps"."player_id") WHERE (p.created_at::date BETWEEN '2014-06-01 00:00:00 UTC'::date AND '2016-06-30 23:59:59 UTC'::date) GROUP BY "registration_month") AS "t2" USING ("registration_month") ORDER BY "t1"."registration_month"