1         Window Functions window_function ( [ ALL ] expression ) OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] ) https://www.sqlshack.com/use-window-functions-sql-server/   1.1         Ranking Window Functions  row_number()/rank()/dense_rank()/NTILE(30) use WideWorldImporters select p.PreferredName,      row_number() over (order by PreferredName ) row_number,      row_number() over (PARTITION BY PreferredName order by PreferredName ) PARTITION_row_number,      rank() over (order by PreferredName ) rank,      dense_rank() over (order by PreferredName ) as dense_rank,      NTILE(30) OVER(ORDER BY PreferredName) AS NTILE_30 from [Application].[People] p   1.2         Aggregate Window Functions SUM(), MAX(), MIN(), AVG(). COUNT() SELECT      row_number() over (order by C.[CustomerID]) rowNo,      C.[CustomerID]      ,C.[CustomerName]      ,o.[OrderDate]      ,o.[CustomerPurchaseOrderNumber]      ,COUNT(o.OrderID) OVER(PARTITION BY o.CustomerID) as   COUNT_CustomerPurchaseOrderNumber      ,AVG(o.OrderID) OVER(PARTITION BY o.CustomerID) as   AVG_CustomerPurchaseOrderNumber      ,SUM(o.OrderID) OVER(PARTITION BY o.CustomerID) as  SUM_CustomerPurchaseOrderNumber      ,MAX(o.OrderID) OVER(PARTITION BY o.CustomerID) as  MAX_CustomerPurchaseOrderNumber      ,MIN(o.OrderID) OVER(PARTITION BY o.CustomerID) as   MIN_CustomerPurchaseOrderNumber FROM  [WideWorldImporters].[Sales].[Customers] c  INNER JOIN [WideWorldImporters].[Sales].[Orders] o ON c.CustomerID = o.CustomerID     1.3         Value Window Functions - LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() SELECT --  row_number() over (order by C.[CustomerID]) rowNo,      C.[CustomerID]      ,C.[CustomerName]      ,o.[OrderDate]      ,LAG(o.OrderDate,1) OVER(PARTITION BY C.CustomerID ORDER BY o.OrderDate) as   LAG_PREV_1      ,LAG(o.OrderDate,2) OVER(PARTITION BY C.CustomerID ORDER BY o.OrderDate) as   LAG_PREV_2      ,LEAD(o.OrderDate,1) OVER(PARTITION BY C.CustomerID ORDER BY o.OrderDate) as   LEAD_NEXT_1      ,LEAD(o.OrderDate,2) OVER(PARTITION BY C.CustomerID ORDER BY o.OrderDate) as  LEAD_NEXT_2      ,FIRST_VALUE(o.OrderDate) OVER(PARTITION BY C.CustomerID ORDER BY C.CustomerID) as FRST_VALUE      ,LAST_VALUE(o.OrderDate) OVER(PARTITION BY C.CustomerID ORDER BY C.CustomerID ) as LST_VALUE FROM  [WideWorldImporters].[Sales].[Customers] c  INNER JOIN [WideWorldImporters].[Sales].[Orders] o ON c.CustomerID = o.CustomerID  ---ORDER BY CustomerID  THIS ORDER BY CLASS SHOULD NOT GIVE - IT WILL GIVE ERROR