title: Get the average of the most recent records within groups with ActiveRecord
body: <p>I have the following query, which calculates the average number of impressions across all teams for a given name and league:</p>
  
  <pre><code>@all_team_avg = NielsenData
                    .where('name = ? and league = ?', name, league)
                    .average('impressions')
                    .to_i
  </code></pre>
  
  <p>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 <code>created_at</code>.</p>
  
  <p>With the help of <a href="https://stackoverflow.com/a/3800572/2324475">this answer</a> I came up with a query which gets the result that I need (I would replace the hard-coded <code>WHERE</code> clause with <code>name</code> and <code>league</code> in the application), but it seems excessively complicated and I have no idea how to translate it nicely into ActiveRecord:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>How can I rewrite this query using ActiveRecord or achieve the same result in a simpler way?</p>