Useful queries for stats retrieval

useful_queries.rb

# Total number of users from last month
User.where(last_sign_in_at: 1.month.ago..Date.today).count
User.where(last_sign_in_at: 1.month.ago..Date.today).order(:last_sign_in_at).pluck(:email, :last_sign_in_at)

# Number of users who updated at least one experiment in the last month
Experiment.joins(:user).where(updated_at: 1.month.ago..Date.today).order(:updated_at).pluck(:updated_at, :id, :title, 'users.email').uniq {|r| r.last}.count
Experiment.joins(:user).where(updated_at: 1.month.ago..Date.today).order(:updated_at).pluck(:updated_at, :id, :title, 'users.email').uniq {|r| r.last}
Experiment.joins(:user).where(updated_at: 1.month.ago..Date.today).order(:updated_at).pluck(:updated_at, :id, :title, 'users.email').uniq {|r| r.last}.map {|a| {a.first.strftime("%b-%Y") => { experiment_title: a[2], user_email: a.last } }}

# Number of experiments,grouped by users, created per month for the past 18 months
User.joins(:experiments).where('experiments.created_at': 18.months.ago..Date.today).group('users.email').count
User.joins(:experiments).where('experiments.created_at': 18.months.ago..Date.today)
                        .order('users.id')
                        .group('users.id')
                        .group_by_month('experiments.created_at', series: false, range: 18.months.ago..Date.today)
                        .count.group_by {|h| h.first.first }
                        .each_with_object({}) {|(k,v), h| h[k] = v.map { |val| { val.first.last.strftime("%b-%Y") => v.last.last } } }..transform_values {|v| v.inject(:merge)}

# Number of users who updated at least one experiment in the last 18 months, per month
User.joins(:experiments).where(experiments: { updated_at: 18.months.ago..Date.today }).distinct.group_by_month('experiments.updated_at', series: false, range: 18.months.ago..Date.today).count.
transform_keys { |k| k.strftime("%b-%Y") }