Tutorial by Examples

Using the Cars Table, we will calculate the total, max, min and average amount of money each costumer spent and haw many times (COUNT) she brought a car for repairing. Id CustomerId MechanicId Model Status Total Cost SELECT CustomerId, SUM(TotalCost) OVER(PARTITION BY Cust...

Using the Item Sales Table, we will try to find out how the sales of our items are increasing through dates. To do so we will calculate the Cumulative Sum of total sales per Item order by the sale date. SELECT item_id, sale_Date SUM(quantity * price) OVER(PARTITION BY item_id ORDER BY sale...

Using the Library Database, we try to find the last book added to the database for each author. For this simple example we assume an always incrementing Id for each record added. SELECT MostRecentBook.Name, MostRecentBook.Title FROM ( SELECT Authors.Name, Books.Title, ...

Let's say that you have exam scores for several exams and you want to divide them into quartiles per exam. -- Setup data: declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int) insert into @values ([Value], ExamId) values (65, 1), (40, 1), (99, 1), (100, 1), (90, 1), ...