Add user cohorts table to admin area

This table shows the percentage of users who registered in the last
twelve months, who last signed in during or later than each of those
twelve months, by month.

It is only enabled when the usage ping is enabled, and the page also
shows pretty-printed usage ping data.

The cohorts table is generated in Ruby from some basic SQL queries,
because performing the gap-filling and running sums needed in both MySQL
and Postgres is painful.

1 files changed, 42 insertions, 0 deletions

diff --git a/spec/services/user_cohorts_service_spec.rb b/spec/services/user_cohorts_service_spec.rb
new file mode 100644
index 00000000000..8d8d0de31cd
--- /dev/null
+++ b/spec/services/user_cohorts_service_spec.rb
@@ -0,0 +1,42 @@
+require 'spec_helper'
+
+describe UserCohortsService do
+  describe '#execute' do
+    def month_start(months_ago)
+      months_ago.months.ago.beginning_of_month.to_date
+    end
+
+    # In the interests of speed and clarity, this example has minimal data.
+    it 'returns a list of user cohorts' do
+      6.times do |months_ago|
+        months_ago_time = (months_ago * 2).months.ago
+
+        create(:user, created_at: months_ago_time, current_sign_in_at: Time.now)
+        create(:user, created_at: months_ago_time, current_sign_in_at: months_ago_time)
+      end
+
+      create(:user) # this user is inactive and belongs to the current month
+
+      expected = {
+        month_start(11) => { months: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(10) => { months: [2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(9) => { months: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(8) => { months: [2, 1, 1, 1, 1, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(7) => { months: [0, 0, 0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(6) => { months: [2, 1, 1, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(5) => { months: [0, 0, 0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(4) => { months: [2, 1, 1, 1, 1], total: 2, inactive: 0 },
+        month_start(3) => { months: [0, 0, 0, 0], total: 0, inactive: 0 },
+        month_start(2) => { months: [2, 1, 1], total: 2, inactive: 0 },
+        month_start(1) => { months: [0, 0], total: 0, inactive: 0 },
+        month_start(0) => { months: [2], total: 2, inactive: 1 }
+      }
+
+      result = described_class.new.execute(12)
+
+      expect(result.length).to eq(12)
+      expect(result.keys).to all(be_a(Date))
+      expect(result).to eq(expected)
+    end
+  end
+end