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:
docs.google.com/spreadsheets/...
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #datascience #analytics

[@avi8016]

Great explanation 💯

[@KARAN-xf2it]

Excellent explanation

[@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

[@sachinkv6972]

SUPER BRO

[@shubhammeshram8504]

excellent..

[@harinee7251]

Thank you so much sir

[@kadhaisollaporom9040]

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

[@ankitbansal6]

My pleasure 😊

[@harinee7251]

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

[@muditmishra9908]

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

[@Satscool786]

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 ?

[@ankitbansal6]

Yes numpy pandas are covered

[@ankitsaxena565]

Nice explanation 🙏

[@ankitbansal6]

Keep watching

[@ur8946]

How you come to number 4 for NTile ?

[@sindhujajittuka9020]

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

[@user-uh2cs7uy7r]

You need to check the date formatting function in the RDBMS you are using. In MySQL, there is date_format() function. So, you can use the formatted date in the WHERE clause, write something like : SELECT DATE_FORMAT("2017-06-15", "%b %d,%Y"); SELECT * FROM hr.employees WHERE DATE_FORMAT(hire_date, "%b %d,%Y")='Jun 17,1987';

[@pavangsk8404]

sir can you make videos on sas for data analytics

[@ankitbansal6]

I have never worked on SAS brother

[@pavangsk8404]

@@ankitbansal6 ok sir ... Your videos on SQL are amazing...thankyou for responding

[@Vicky-ne9mt]

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

[@ankitbansal6]

Using loc method

[@Vicky-ne9mt]

@@ankitbansal6 what?

[@apna9656]

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

[@ankitbansal6]

kzbin.info/www/bejne/hWfQdZeLfturb80

[@theraizadatalks14]

/* CREATE TABLE revenue_data ( Name VARCHAR(50), Month VARCHAR(50), Revenue INT ); INSERT INTO revenue_data (Name, Month, Revenue) VALUES ('Adi', 'Jan', 1000), ('Adi', 'Feb', 2000), ('Jack', 'Mar', 3000); */ -------------------------------------------------- Select * from revenue_data -------------------------------------------------- --1. Using Case When and Groupby Select Name, ISNULL(SUM(Case when month='Jan' then revenue else null end),0) as [Jan_rev], ISNULL(SUM(Case when month='Feb' then revenue else null end),0) as [Feb_rev], ISNULL(SUM(Case when month='Mar' then revenue else null end),0) as [Mar_rev] from revenue_data group by Name -------------------------------------------------- -- 2. Using Pivot Function Select Name,isnull(Jan,0) as [Jan_rev] ,isnull(Feb,0) as [Feb_rev],isnull(Mar,0) as [Mar_rev] from ( Select * from revenue_data ) SourceTbl Pivot ( MAX(revenue) for month in (Jan,Feb,Mar) --([Jan],[Feb],[Mar]) ) PivotTbl --------------------------------------------------

[@bodybuildingmotivation5438]

select NAME ,sum(case when Month ='Jan' then revenue end) as Jan_rev ,sum(case when Month ='Feb' then revenue end) as Feb_rev ,sum(case when Month ='Mar' then revenue end) as Mar_rev from revenue_data group by name

[@NikhilKumar-ik9fw]

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)

[@arjundev4908]

WITH CTE AS(SELECT *, ROW_NUMBER()OVER(PARTITION BY COUNTRY ORDER BY MAJORITY DESC) AS ID FROM MYNTRA) SELECT NAME FROM CTE WHERE ID%100 = 0; I hope this should help you.

[@NikhilKumar-ik9fw]

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

[@theraizadatalks14]

Hi Can you please clarify the question, what do you mean by Majority ( Try to give a example of small dataset ) ?

[@NikhilKumar-ik9fw]

@@theraizadatalks14 Its the majority of votes won in elections.