CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordrails-activerecord
Sam
Sam

Reputation: 5014

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

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?

Upvotes: 4

Views: 121

Answers (1)

Shelvacu
Shelvacu

Reputation: 4380

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...")

Upvotes: 3

Related Questions