Sql for average daily increase

I've got a db table that has a date field, a username field, and an integer points balance field. There is a record for every day, storing the balance at the end of the business day.

I'm looking for the most efficient way of calculating the average daily increase for e开发者_JAVA技巧ach user, sorting highest average daily increase to the lowest.

---

This should work for MS SQL Server. It assumes that there truly is one entry per date per user, with no gaps in dates, and no mucking around with time (hours, minutes, seconds) values. (Also, no null values!) It will calculate the average daily increase, whether or not it actually goes up ever day.

SELECT mt.UserName, avg(mt.Balance - mt2.Balance) AvgDailyIncrease
 from MyTable mt
  inner join MyTable mt2
   on mt2.UserName = mt.UserName
    and mt2.CaptureDate = dateadd(dd, -1, mt.CaptureDate)
 group by mt.UserName
 order by avg(mt.Balance - mt2.Balance) desc

---

Assuming that the balance always increases, you could just find the balance on the first day, the balance on the last day, and calculate the average (based on the # of days):

;with minmax as ( -- subquery to get min / max data per user
    select
        username
        ,min(capturedate) as mincapturedate
        ,min(balance) as minbalance
        ,max(capturedate) as maxcapturedate
        ,max(balance) as maxbalance
    from
        [5171722] t
    group by username
)
,averageincrease as ( -- subquery to calculate average daily increase
    select
        username
        ,datediff(day, mincapturedate, maxcapturedate) as numdays
        ,(maxbalance - minbalance) as totalincrease
        ,(maxbalance - minbalance) / datediff(day, mincapturedate, maxcapturedate) as
            averagedailyincrease
    from
        minmax
)
-- pull results together, with highest average daily increase first
select
    *
from
    averageincrease
order by
    averagedailyincrease desc

The field averagedailyincrease at the end contains the average daily increase.