Average of last 4 runs of a product on SQL Server 2008
with cte as 
( select ID, cost, ddate 
       , row_number() over (partition by ID order by ddate) as rn 
    from table 
)
select cte1.ID, cte1.ddate, avg(cte2.cost) as avg, count(cte2.cost) as count
from cte as cte1
join cte as cte2
      on cte2.ID  = cte1.ID  
     and cte2.rn &gt;= cte1.rn 
     and cte2.rn &lt;= cte1.rn + 3
group by cte1.ID, cte1.ddate 
order by cte1.ID, cte1.ddate