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.
You can now support me on patreon - / beardeddev
To improve your T-SQL skills check out my recommended reading list:
T-SQL Querying
www.amazon.com/gp/product/073...
T-SQL Fundamentals
www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
www.amazon.com/gp/product/073...
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-pb9jg]

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

[@BeardedDevData]

Excellent.

[@pokepoke3]

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

[@giorgosi.fovakis8810]

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

[@amalal-suhaimi9810]

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 ?

[@BeardedDevData]

In that case you will need to change the order by to be by rental duration.

[@Khaled_Abdelaal]

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

[@BeardedDevData]

Hi, you can either contact me on here or email me at [e-mail]

[@Khaled_Abdelaal]

@@BeardedDevData Thanks. I will drop you an email

[@pabeader1941]

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??

[@BeardedDevData]

Yes, there was an issue with the end screen but because I had worked through all the examples it was uploaded. The story regarding the million rows actually finishes, I just explain that if you have that many rows you can divide them in to tiles then select a tile to work with.

[@pabeader1941]

@@BeardedDevData okay. I'm just used to your wind down at the end. sall gud.