diff options
author | Claire <claire.github-309c@sitedethib.com> | 2021-10-14 22:57:41 +0200 |
---|---|---|
committer | GitHub <noreply@github.com> | 2021-10-14 22:57:41 +0200 |
commit | b6f24ef0fb8b594c38c29090518c21af051b63b7 (patch) | |
tree | 5cec6a6b13dfd062848c74ae8e041c589ae40d00 /app/lib/admin/metrics/retention.rb | |
parent | ebf2c3195615bb524f6908e84f99887c8775cbc3 (diff) | |
parent | 6964952d5f242ca936de9df361185b3e70a99ca4 (diff) |
Merge pull request #1622 from ClearlyClaire/glitch-soc/merge-upstream
Merge upstream changes
Diffstat (limited to 'app/lib/admin/metrics/retention.rb')
-rw-r--r-- | app/lib/admin/metrics/retention.rb | 67 |
1 files changed, 67 insertions, 0 deletions
diff --git a/app/lib/admin/metrics/retention.rb b/app/lib/admin/metrics/retention.rb new file mode 100644 index 000000000..6b9dfde49 --- /dev/null +++ b/app/lib/admin/metrics/retention.rb @@ -0,0 +1,67 @@ +# frozen_string_literal: true + +class Admin::Metrics::Retention + class Cohort < ActiveModelSerializers::Model + attributes :period, :frequency, :data + end + + class CohortData < ActiveModelSerializers::Model + attributes :date, :percent, :value + end + + def initialize(start_at, end_at, frequency) + @start_at = start_at&.to_date + @end_at = end_at&.to_date + @frequency = %w(day month).include?(frequency) ? frequency : 'day' + end + + def cohorts + sql = <<-SQL.squish + SELECT axis.*, ( + WITH new_users AS ( + SELECT users.id + FROM users + WHERE date_trunc($3, users.created_at)::date = axis.cohort_period + ), + retained_users AS ( + SELECT users.id + FROM users + INNER JOIN new_users on new_users.id = users.id + WHERE date_trunc($3, users.current_sign_in_at) >= axis.retention_period + ) + SELECT ARRAY[count(*), (count(*))::float / (SELECT GREATEST(count(*), 1) FROM new_users)] AS retention_value_and_rate + FROM retained_users + ) + FROM ( + WITH cohort_periods AS ( + SELECT generate_series(date_trunc($3, $1::timestamp)::date, date_trunc($3, $2::timestamp)::date, ('1 ' || $3)::interval) AS cohort_period + ), + retention_periods AS ( + SELECT cohort_period AS retention_period FROM cohort_periods + ) + SELECT * + FROM cohort_periods, retention_periods + WHERE retention_period >= cohort_period + ) as axis + SQL + + rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at], [nil, @frequency]]) + + rows.each_with_object([]) do |row, arr| + current_cohort = arr.last + + if current_cohort.nil? || current_cohort.period != row['cohort_period'] + current_cohort = Cohort.new(period: row['cohort_period'], frequency: @frequency, data: []) + arr << current_cohort + end + + value, rate = row['retention_value_and_rate'].delete('{}').split(',') + + current_cohort.data << CohortData.new( + date: row['retention_period'], + percent: rate.to_f, + value: value.to_s + ) + end + end +end |