query_hugo.sql

SELECT
  date_trunc('month', ph1.created_at)             AS date,
  SUM(SUM(ph2.after - ph2.before))
  OVER (
    ORDER BY date_trunc('month', ph1.created_at)) AS mrr,
  SUM(CASE WHEN ph1.event = 'new'
    THEN (ph1.after - ph1.before)
      ELSE 0 END)                                            AS new,
  SUM(CASE WHEN ph1.event = 'upsell'
    THEN (ph1.after - ph1.before)
      ELSE 0 END)                                            AS upsell,
  SUM(CASE WHEN ph1.event = 'churn'
    THEN (ph1.after - ph1.before)
      ELSE 0 END)                                            AS churn,
  SUM(CASE WHEN ph1.event = 'downsell'
    THEN (ph1.after - ph1.before)
      ELSE 0 END)                                            AS downsell,
  SUM(CASE WHEN ph1.event = 'cancel'
    THEN (ph1.after - ph1.before)
      ELSE 0 END)                                            AS cancel
FROM "plan_histories" ph1
  LEFT JOIN plan_histories AS ph2
   ON ph2.id = ph1.id
      AND ph2.plan_id NOT IN (
	SELECT plan_id
	FROM plan_histories
	WHERE event = 'cancel'
      )
WHERE (ph1.created_at < '2016-11-08 23:59:59.999999')
GROUP BY date
ORDER BY date DESC;