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