Facebook
From Voluminous Motmot, 7 Years ago, written in SQL.
Embed
Download Paste or View Raw
Hits: 325
  1. SELECT "t2"."lt",
  2.        COALESCE(t2.deposits_total, 0) AS "deposits_total",
  3.        COALESCE(t2.players_total, 0) AS "players_total",
  4.        COALESCE(t2.registrations_count, 0) AS "registrations_count",
  5.        "t2"."in_out_total",
  6.        "t1"."registration_month",
  7.        "t1"."deposit_month",
  8.        "t1"."players_count",
  9.        "t1"."deposits_count",
  10.        "t1"."deposits_sum"
  11. FROM
  12.   (SELECT "registration_month",
  13.           array_to_json(array_agg(t1.deposit_month
  14.                                   ORDER BY deposit_month)) AS deposit_month,
  15.           array_to_json(array_agg(t1.players_count
  16.                                   ORDER BY deposit_month)) AS players_count,
  17.           array_to_json(array_agg(t1.deposits_count
  18.                                   ORDER BY deposit_month)) AS deposits_count,
  19.           array_to_json(array_agg(t1.deposits_sum
  20.                                   ORDER BY deposit_month)) AS deposits_sum
  21.    FROM (WITH "stats" AS
  22.            (SELECT date_trunc('month', p.created_at) AS registration_month,
  23.                    date_trunc('month', d.created_at) AS deposit_month,
  24.                    COUNT(DISTINCT d.player_id) AS players_count,
  25.                    SUM(d.amount) AS deposits_sum,
  26.                    SUM(1) AS deposits_count
  27.             FROM "deposits" AS "d"
  28.             INNER JOIN "players" AS "p" ON ("p"."id" = "d"."player_id")
  29.             INNER JOIN "player_stats" AS "ps" ON ("p"."id" = "ps"."player_id")
  30.             WHERE ((p.created_at::DATE BETWEEN '2014-06-01 00:00:00 UTC'::DATE AND '2016-06-30 23:59:59 UTC'::DATE)
  31.                    AND ("d"."amount" > 0))
  32.             GROUP BY date_trunc('month', d.created_at),
  33.                      date_trunc('month', p.created_at)),
  34.               "reg_months" AS
  35.            (SELECT generate_series('2014-06-01 00:00:00 UTC'::DATE, '2016-06-30 23:59:59 UTC'::DATE, '1 month') AS MONTH),
  36.               "dep_months" AS
  37.            (SELECT generate_series('2014-06-01 00:00:00 UTC'::DATE, '2016-06-30 23:59:59 UTC'::DATE, '1 month') AS MONTH)
  38.          SELECT "reg_months"."month" AS "registration_month",
  39.                 "dep_months"."month" AS "deposit_month",
  40.                 COALESCE(stats.players_count, 0) AS "players_count",
  41.                 COALESCE(stats.deposits_count, 0) AS "deposits_count",
  42.                 COALESCE(stats.deposits_sum, 0) AS "deposits_sum"
  43.          FROM "reg_months"
  44.          CROSS JOIN "dep_months"
  45.          LEFT JOIN "stats" ON (("reg_months"."month" = "stats"."registration_month")
  46.                                AND ("dep_months"."month" = "stats"."deposit_month"))
  47.          WHERE ("reg_months"."month" <= "dep_months"."month")) AS "t1"
  48.    GROUP BY "registration_month") AS "t1"
  49. LEFT JOIN
  50.   (SELECT AVG(EXTRACT(epoch
  51.                       FROM age(last_deposit_created_at, first_deposit_created_at)) / EXTRACT(epoch
  52.                                                                                              FROM INTERVAL '1 day')) AS lt,
  53.           SUM(deposits_sum) AS deposits_total,
  54.           SUM(deposits_sum - withdraws_sum) AS in_out_total,
  55.           date_trunc('month', p.created_at) AS registration_month,
  56.           COUNT(*) AS registrations_count,
  57.           SUM(CASE WHEN deposits_sum > 0 THEN 1 ELSE 0 END) AS players_total
  58.    FROM "players" AS "p"
  59.    LEFT JOIN "player_stats" AS "ps" ON ("p"."id" = "ps"."player_id")
  60.    WHERE (p.created_at::DATE BETWEEN '2014-06-01 00:00:00 UTC'::DATE AND '2016-06-30 23:59:59 UTC'::DATE)
  61.    GROUP BY "registration_month") AS "t2" USING ("registration_month")
  62. ORDER BY "t1"."registration_month"