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"