SQL Server: The Magical OVER clause

When comes to query writing, I think, I am so lazy ;) and always prefer less code. Recently I found OVER clause so helpful to write my code in more effect and more magical way. Most of us know the usage of OVER clause, when creating row number column for a given partition or even with out any partition (especially when continuous row number is required for all records). 

USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
,ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY ProductID) AS GroupRowNumber
,ROW_NUMBER() OVER(ORDER BY ProductID) AS ContRowNumber
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

But I think most magical part of OVER clause is, when we use aggregate functions for a given partition. Through this method we can avoid lengthy and complex sub queries. Here is an example from msdn library.

USE AdventureWorks;

GO

SELECT SalesOrderID, ProductID, OrderQty

,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'

,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'

,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'

,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'

,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'

FROM Sales.SalesOrderDetail 

WHERE SalesOrderID IN(43659,43664);

GO