Queries on age and has_teacher

gistfile1.rb

# count average number of puzzles completed
def trophies(age, has_teacher, start_date = "2012-01-01", end_date = "2015-01-01")
  age = "TIMESTAMPDIFF(YEAR, users.birthday, CURRENT_DATE) = #{age}"
  has_teacher = "#{has_teacher ? 'IN' : 'NOT IN'} (SELECT DISTINCT(student_user_id) FROM followers)"
  UserLevel.joins(:user).where("user_levels.best_result >= 20 AND users.id #{has_teacher} AND #{age} AND users.created_at > '#{start_date}' AND users.created_at < '#{end_date}'").count.to_f / User.where("id #{has_teacher} AND #{age} AND created_at > '#{start_date}' AND created_at < '#{end_date}'").count
end

[true, false].map{|has_teacher| (4..21).map{|age| trophies(age, has_teacher)}}
[true, false].map{|has_teacher| (4..21).map{|age| trophies(age, has_teacher, "2014-09-01")}}


# count average number of trophies earned
def trophies(age, has_teacher, start_date = "2012-01-01", end_date = "2015-01-01")
  age = "TIMESTAMPDIFF(YEAR, users.birthday, CURRENT_DATE) = #{age}"
  has_teacher = "#{has_teacher ? 'IN' : 'NOT IN'} (SELECT DISTINCT(student_user_id) FROM followers)"
  UserTrophy.joins(:user).where("users.id #{has_teacher} AND #{age} AND users.created_at > '#{start_date}' AND users.created_at < '#{end_date}'").count.to_f / User.where("id #{has_teacher} AND #{age} AND created_at > '#{start_date}' AND created_at < '#{end_date}'").count
end

[true, false].map{|has_teacher| (0..21).map{|age| trophies(age, has_teacher)}}