Moving Average PER TICKER for each day

Question

I am trying to calculate the, say, 3 day moving average (in reality 30 day) volume for stocks. I'm trying to get the average of the last 3 date entries (rather than today-3 days). I've been trying to do something with rownumber in SQL server 2012 but with no success. Can anyone help out. Below is a template schema, and my rubbish attempt at SQL. I have various incarnations of the below SQL with the group by's but still not working. Many thanks! ``` select dt_eod, ticker, volume from ( select dt_eod, ticker, avg(volume) row_number() over(partition by dt_eod order by max_close desc) rn from mytable ) src where rn >= 1 and rn <= 3 order by dt_eod ``` Sample Schema: ``` CREATE TABLE yourtable ([dt_date] int, [ticker] varchar(1), [volume] int); INSERT INTO yourtable ([dt_date], [ticker], [volume]) VALUES (20121201, 'A', 5), (20121201, 'B', 7), (20121201, 'C', 6), (20121202, 'A', 10), (20121202, 'B', 8), (20121202, 'C', 7), (20121203, 'A', 10), (20121203, 'B', 87), (20121203, 'C', 74), (201…

Accepted Answer

Three day average for each row:

```
with top3Values as
(
  select t.ticker, t.dt_date, top3.volume
  from yourtable t
    outer apply
    (
      select top 3 top3.volume
      from yourtable top3
      where t.ticker = top3.ticker
        and t.dt_date >= top3.dt_date
      order by top3.dt_date desc
    ) top3
)
select ticker, dt_date, ThreeDayVolume = avg(volume)
from top3Values
group by ticker, dt_date
order by ticker, dt_date
```

SQL Fiddle demo.

Latest value:

```
with tickers as
(
  select distinct ticker from yourtable
), top3Values as
(
  select t.ticker, top3.volume
  from tickers t
    outer apply
    (
      select top 3 top3.volume
      from yourtable top3
      where t.ticker = top3.ticker
      order by dt_date desc
    ) top3
)
select ticker, ThreeDayVolume = avg(volume)
from top3Values
group by ticker
order by ticker
```

SQL Fiddle demo.

Realistically you wouldn't need to create the tickers CTE for the second query, as you'd be basing this on a `[ticker]` table, and you'd probably have some sort of `date` parameter in the query, but hopefully this will get you on the right track.

Moving Average PER TICKER for each day

Solution

Problem