ActiveRecord How-To: Multiple Aggregate Counts Grouped by Day

I was trying to figure out a clean way to generate internal metrics to track how often users are taking action on a site. This lets us know if users are becoming more or less engaged, and whether or not they are likely to continue using the service.

In this particular site, I have two tables, User and Match, with every user having many "matches".

For every "match" the user sees, he can take an action: respond, favorite, or ignore. On the match table, we have fields action_taken and action_taken_on to record what action the user took on the match and when.

For my metrics, I want to split the user's actions by day, so I can see a table showing for each day how many responses, favorites, and ignores the user performed.

In PostgreSQL, here is the solution I came up with.

class User

  has_many :matches

  def daily_action_breakdown
    matches.select("
      DATE(action_taken_on) AS day,
      SUM(CASE WHEN matches.action_taken = 'responded' THEN 1 ELSE 0 END) AS responded,
      SUM(CASE WHEN matches.action_taken = 'favorited' THEN 1 ELSE 0 END) AS favorited,
      SUM(CASE WHEN matches.action_taken = 'ignored' THEN 1 ELSE 0 END) AS ignored
    ").where('action_taken_on IS NOT NULL').group('day').order('day desc')
  end

end

The view result:

View Result