Amazon Interview Question | What is NTILE with syntax?
Question
TheDataMonk
Follow Me
About TheDataMonkGrand Master
I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)Follow Me
Related questions
The Given Data set, Table has transitive dependencies. Explain Which Attributes are partially dependent. How will you overcome this? Explain the approach?
Write a query to Plot a Relationship between the highest followers in every country (Country Wise Highest Followers) and show which country has the highest followers and which country has the lowest one. Explain the approach?
Write a Query to Find the account with the highest number of followers in every country. Explain the approach?
Assign Rank To each Category by authentic engagement in each country and return the category with a rank less than 3. Explain the Approach?
Write a Query to check for Missing values in the given table? Explain how will you deal with the missing data for qualitative and quantitative data?
Answers ( 3 )
NTILE() function distributes the rows in an ordered partition into specific
number of groups.It assigns each group a bucket number starting from one.
For Example,
NTILE(10) will divide the 100 rows into 10 groups, with each group
consisting of 10 rows.
If the groups are not equally divided, the function will set more rows
to the starting groups and less to the following groups.
Now, suppose we have employee table with 2 columns as
empname and salary and having 6 records.
The following query will divide those 6 rows into 3 buckets
and number them as 1, 2 and 3 according to their salary.
Query:
SELECT empname,
NTILE (3) OVER (
ORDER BY salary DESC
) buckets
FROM employees;
The SQL Server NTILE() is a window function that distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.
The syntax of the NTILE() function is as follows:
NTILE(buckets) OVER (
[PARTITION BY partition_expression, … ]
ORDER BY sort_expression [ASC | DESC], …
)
NTILE() function divides rows in a sorted partition into a specific number of groups.
Each group will be assigned a bucket number starting at one.
For each row, the NTILE() function returns a bucket number representing the group to which the row belongs.
NTILE(n) OVER (
PARTITION BY expression
ORDER BY expression [ASC|DESC],
n is a positive integer which ranges from 1 to n