SQL Tutorial - How to use NTILE in SQL Server

Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.

To improve your T-SQL skills check out my recommended reading list:

T-SQL Querying

T-SQL Fundamentals

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

In this SQL Tutorial we cover an overview of NTILE and the syntax, a discussion of the difference between partitions and tiles. We also demonstrate a particularly poor example of using NTILE and typical use cases.

To follow along with the video please use scripts below:
IF OBJECT_ID(N'dbo.Sale', N'U') IS NOT NULL
DROP TABLE dbo.Sale

IF OBJECT_ID(N'dbo.SaleChannel', N'U') IS NOT NULL
DROP TABLE dbo.SaleChannel

CREATE TABLE SaleChannel
(
SaleChannelId INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_SalesChannel_SaleChannelId PRIMARY KEY (SaleChannelId)
, SaleChannel VARCHAR(10) NOT NULL
)

INSERT INTO dbo.SaleChannel (SaleChannel)
VALUES ('In-Store'), ('Online')

CREATE TABLE Sale
(
SaleId INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_Sales_SaleId PRIMARY KEY (SaleId)
, SaleChannelId INT NOT NULL
CONSTRAINT FK_Sales_SaleChannelId FOREIGN KEY (SaleChannelId) REFERENCES SaleChannel (SaleChannelId)
, SaleDate DATE NOT NULL
, SaleAmount DECIMAL(6, 2) NOT NULL
)

INSERT INTO dbo.Sale (SaleChannelId, SaleDate, SaleAmount)
VALUES (1, '20180101', 15.99)
, (1, '20180201', 7.49)
, (1, '20180301', 14.99)
, (1, '20180401', 6.49)
, (1, '20180501', 13.99)
, (1, '20180601', 5.49)
, (1, '20180701', 19.99)
, (1, '20180801', 10.49)
, (1, '20180901', 18.99)
, (1, '20181001', 11.49)
, (1, '20181101', 17.99)
, (1, '20181201', 12.49)
, (2, '20180101', 16.99)
, (2, '20180201', 13.49)
, (2, '20180301', 15.99)
, (2, '20180401', 14.49)
, (2, '20180501', 19.99)
, (2, '20180601', 7.49)

[sujaybshah]

You're a gem in explaining window functions(so far I checked out the SQL window functions video, will check out others soon)

[OldPick-Unix-dude-pbjg]

Appreciate the explanation on the NTILE - used it create sample data.

[pokepoke]

I just started learning about window functions, and you've been the most helpful. Thank you.

[giorgosi.fovakis]

Great! and nice example with CTE in the end as well

[Khaled_Abdelaal]

@BeardedDev: I have one technical question. How can I contact you, please?

[amalal-suhaimi]

Can you provide a table with the movie titles and divide them into 4 levels (first_quarter, second_quarter, third_quarter, and final_quarter) based on the quartiles (25%, 50%, 75%) of the rental duration for movies across all categories?

NTILE(4) OVER (ORDER BY title ) AS standard_quartiles >>>> is it right ?

[pabeader]

I know this is an old video, but did you know that it cuts off before you complete the story about the 1 million rows of data??