Tutorial by Examples

Calculate a 6-month (126-business-day) centered moving average of a price: SELECT TradeDate, AVG(Px) OVER (ORDER BY TradeDate ROWS BETWEEN 63 PRECEDING AND 63 FOLLOWING) AS PxMovingAverage FROM HistoricalPrices Note that, because it will take up to 63 rows before and after each returned row, at...

In tables recording events there is often a datetime field recording the time an event happened. Finding the single most recent event can be difficult because it's always possible that two events were recorded with exactly identical timestamps. You can use row_number() over (order by ...) to make su...

Moving Average of last 30 Items sold SELECT value_column1, ( SELECT AVG(value_column1) AS moving_average FROM Table1 T2 WHERE ( SELECT COUNT(*) FROM Table1 T3 WHERE date_column1 BETWEEN T2.date_column1...