SQL For Data Science | NTILE Analytical Function | Top and Bottom N Percent Customers

In this video we are going to learn a SQL analytical function called NTILE which is used in data science to create customer distribution based on sales. We will use the orders dataset to understand it via solving a problem where we need to find top 25 and bottom 25 percent customers be sales.

here is the orders dataset link:

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #datascience #analytics

[stat_life]

#SUMMARY
#REGION WISE SEGMENTATION OF CUSTOMER ON BASIS OF TOTAL SALES
--NTILE(x) : It is a window function which divides the entire dataset into x percentiles on basis of windows.

#### MS SQL ####

WITH testcase AS
(
SELECT
customer_name,
region,
SUM(sales) as total_sales
FROM
orders
GROUP BY
customer_name,
region
ORDER BY
region,
total_sales DESC
)

SELECT *,
NTILE(4) OVER(PARTITION BY region ORDER BY SUM(SALES) DESC) AS segment

FROM
testcase

[muditmishra]

We can also use : PERCENTILE_CONT() function followed by where clause.

[harinee]

Sir, kindly can you provide a detailed roadmap of how to become a data engineer..it will be very useful

[kadhaisollaporom]

Thankyou for these videos its really helpful for the aspiring data professionals thankyou ❤

[Satscool]

Ankit bhai thanku for all the videos u are providing here, really helpful
Just wanted to confirm that the python course u have on namstesql also covers numpy pandas or other data analysis part?
And do u also help in resume creation n all ?

[pavangsk]

sir can you make videos on sas for data analytics

[apna]

Hi Ankit sir
In the interview they asked me one question they provided me below table


Name. month. Revenue (col name)
Adi. Jab. 1000
Adi. Feb. 2000
Jack. Mar. 3000


They want an answer in below format

Name. Jab_rev. Feb_rev. Mar_rev


How can we do this? Please let us know

[NikhilKumar-ikfw]

Bro I wasn't able to answer this question in Myntra interview and haven't found any helpful Relevant solution, please so help me

given three columns "Name of the person won", "Country", "Majority" Assume this whole data set is unique and consists thousands of rows. Give me the data for 100th person from every country while ordering majority in descending order Please give SQL logic

Without using rank( window function)

[NikhilKumar-ikfw]

Bro I wasn't able to answer this question in Myntra interview and haven't found any helpful Relevant solution, please so help me

given three columns "Name of the person won", "Country", "Majority" Assume this whole data set is unique and consists thousands of rows. Give me the data for 100th person from every country while ordering majority in descending order Please give SQL logic

Without using rank( window function

Please do a video on this or please provide reference video link if done. I hope you'll make a video

[Vicky-nemt]

hii can anyone suggest me a good website to parctice sql(like interview questions )

[sindhujajittuka]

Suppose we have a table with column name hiredate (format yyyy-mm-dd)
We need to find the hire date in the format like ex:feb 22, 1991.
Can anyone provide me the solution for this kind of prob...