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_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:
Follow David on Twitter
It's what all the cool kids do.