## 10 Questions, 10 Minutes – 1/100

This is something which has been on my mind since a long time. We will be picking 10 questions per day and would like to simplify it.

We will make sure that the complete article is covered in 10 minutes by the reader. There will be 100 posts in the coming 3 months.

The articles/questions will revolve around SQL, Statistics, Python/R, MS Excel, Statistical Modelling, and case studies.

The questions will be a mix of these topics to help you prepare for interviews

You can also contribute by framing 10 questions and sending it to contact@thedatamonk.com or messaging me on Linkedin.

The questions will be updated late in the night ~1-2 a.m. and will be posted on Linkedin as well.

Let’s see how many can we solve in the next 100 posts**1.** **Write the syntax to create a new column using Row Number over the Salary column**

SELECT *, ROW_NUMBER() OVER (Order By Salary) as Row_Num

FROM Employee**Output**

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 2 |

124 | Aman | 40000 | 3 |

123 | Amit | 50000 | 4 |

453 | Sumit | 50000 | 5 |

**2. What is PARTITION BY clause?**

PARTITION BY clause is used to create a partition of ranking in a table. If you partition by Salary in the above table, then it will provide a ranking based on each unique salary. Example below:-

SELECT *, ROW_NUMBER() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 2 |

124 | Aman | 40000 | 1 |

123 | Amit | 50000 | 1 |

453 | Sumit | 50000 | 2 |

**3. What is a RANK() function? How is it different from ROW_NUMBER()?**

– RANK() function gives ranking to a row based on the value on which you want to base your ranking. If there are equal values, then the rank will be repeated and the row following the repeated values will skip as many ranks as there are repeated values row. Confused?? Try out the example below:-

SELECT *, RANK() OVER (ORDER BY Salary) as Row_Num

FROM Employee

Output

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 1 |

124 | Aman | 40000 | 3 |

123 | Amit | 50000 | 4 |

453 | Sumit | 50000 | 4 |

As you can see, the rank 2 has been skipped because there were two employees with the same Salary and the result is ordered in ascending order by default.

**4. What is Dense Ranking?**

– DENSE_RANK() is similar to the RANK() function but it does not skip any rank, so if there are two equal values then both will be termed as 1, the third value will be termed as 3 and not 2.

Syntax:-

SELECT *, DENSE_RANK() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num

FROM Employee

Output:-

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 1 |

124 | Aman | 40000 | 3 |

123 | Amit | 50000 | 4 |

453 | Sumit | 50000 | 4 |

432 | Nihar | 60000 | 6 |

**5.** **What is NTILE() function?**

-NTILE() is similar to percentile NTILE(3) will divide the data in 3 parts.

SELECT *, NTILE() OVER (ORDER BY Salary) as Ntile

FROM Employee

The number of rows should be 6/3 = 2, therefore we need to divide the 2 rows for each percentile

Emp. ID | Name | Salary | Ntile |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 1 |

124 | Aman | 40000 | 2 |

123 | Amit | 50000 | 2 |

453 | Sumit | 50000 | 3 |

432 | Nihar | 60000 | 3 |

**6. How to get the second highest salary from a table?**

Select MAX(Salary)

from Employee

Where Salary NOT IN (SELECT MAX(Salary) from Employee)

**7. Find the 3rd Maximum salary in the employee table**

-Select distinct sal

from emp e1

where 3 = ((select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

**8. Get all employee detail from EmployeeDetail table whose “FirstName” not start with any single character between ‘a-p’**

– SELECT *

FROM EmployeeDetail

WHERE FirstName like ‘[^a-p]%’

**9. How to fetch only even rows from a table?**

-The best way to do it is by adding a row number using ROW_NUMBER() and then pulling the alternate row number using row_num%2 = 0

Suppose, there are 3 columns in a table i.e. student_ID, student_Name, student_Grade. Pull the even rows

SELECT *

FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_ID) as row_num FROM student) x

WHERE x.row_num%2=0

**10. How to fetch only odd rows from the same table?**

-Simply apply the x.row_num%2 <> 0 to get the odd rows

SELECT *

FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_ID) as row_num FROM student) x

WHERE x.row_num%2 <> 0

Let us know if you think I need to change any answer here.

Keep Learning 🙂

The Data Monk

## Leave a reply