Get the average of the most recent records within groups with ActiveRecord

Question

I have the following query, which calculates the average number of impressions across all teams for a given name and league:

@all_team_avg = NielsenData
                  .where('name = ? and league = ?', name, league)
                  .average('impressions')
                  .to_i

However, there can be multiple entries for each name/league/team combination. I need to modify the query to only average the most recent records by created_at.

With the help of this answer I came up with a query which gets the result that I need (I would replace the hard-coded WHERE clause with name and league in the application), but it seems excessively complicated and I have no idea how to translate it nicely into ActiveRecord:

SELECT avg(sub.impressions) 
    FROM (
    WITH summary AS (
        SELECT n.team, 
               n.name,
               n.league,
               n.impressions, 
               n.created_at,
               ROW_NUMBER() OVER(PARTITION BY n.team 
                            ORDER BY n.created_at DESC) AS rowcount
        FROM nielsen_data n
        WHERE n.name = 'Social Media - Twitter Followers' 
          AND n.league = 'National Football League'
    )  
    SELECT s.*
    FROM summary s
    WHERE s.rowcount = 1) sub;

How can I rewrite this query using ActiveRecord or achieve the same result in a simpler way?

Do you want it to be resolved in a pure SQL way or is an hybrid solution acceptable. Some operations can be done in the ruby side. — Uelb, Mar 16 '16 at 17:38
Anything that gets it done is fine with me, but performance is a consideration. So for example I would prefer to avoid doing something like pulling in the entire table and processing it in-memory. I'm not very familiar with Ruby so I'm open to suggestions. — Sam, Mar 16 '16 at 17:43
How many duplicate records are there going to be? If only a few then you can group by date and filter later in ruby — Vasfed, Mar 16 '16 at 17:45
Right now there are only four per set. I don't see it getting too much more than that, potentially up to 10 or 20 in the future. — Sam, Mar 16 '16 at 17:49

score 3 · Accepted Answer · answered Mar 16 '16 at 17:38

3

When all you have is a hammer, everything looks like a nail.

Sometimes, raw SQL is the best choice. You can do something like:

@all_team_avg = NielsenData.find_by_sql("...your_sql_statement_here...")

answered Mar 16 '16 at 17:38

Shelvacu

4,245
25
44

Thanks, this works fine and may be my best option. I'm going to leave this open for a bit because I would prefer to do it without a big in-line SQL query, but otherwise I will accept your answer. – Sam Mar 16 '16 at 18:07

Get the average of the most recent records within groups with ActiveRecord

1 Answers1