This is our second problem statement in the SQL Interview Questions series. In this series we are committed to bring out a collection of real world SQL interview questions, we have also shared the video solution for each question.
To check all the interview questions available in the series, click here.
SQL is a very important skill for the data science/analytics professionals and SQL window function problems are very frequently asked in the interviews. Mastering window functions will play a key role in your job interview success.
Let us first take a look at the types of window functions available in SQL:
- Aggregate Window Functions: SUM(), MAX(), MIN(), AVG(). COUNT()
- Ranking Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), CUME_DIST(), PERCENT_RANK()
- Value Window Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
Now, let’s test yourself with a real SQL interview question.
Problem Statement: Difference between the average rating and the second last rating of employees
Find the difference between the average rating and the second last rating of all the employees in the Employee table. Output the employee’s name, average rating, second last rating and the difference between the two ratings, output the rating difference up to 1 decimal place only.
SQL Table: Employee
Employee
| EmpName | EmpID | Department | Year | Rating |
|---|---|---|---|---|
| Peter | E5489 | Marketing | 2020 | 8 |
| Peter | E5489 | Marketing | 2019 | 7 |
| Peter | E5489 | Marketing | 2018 | 6.5 |
| Peter | E5489 | Marketing | 2017 | 8.5 |
| Peter | E5489 | Marketing | 2016 | 7 |
| Philip | E1027 | HR | 2020 | 8 |
| Philip | E1027 | HR | 2019 | 8.5 |
| Shirley | E0589 | Finance | 2020 | 7 |
| Shirley | E0589 | Finance | 2019 | 5 |
| Shirley | E0589 | Finance | 2018 | 6 |
| Shirley | E0589 | Finance | 2017 | 7 |
| William | E4128 | Sales | 2020 | 8 |
| William | E4128 | Sales | 2019 | 6.5 |
| William | E4128 | Sales | 2018 | 7 |
| Jessica | E6987 | HR | 2020 | 9 |
| Jessica | E6987 | HR | 2019 | 9 |
| Jessica | E6987 | HR | 2018 | 8.5 |
| Jessica | E6987 | HR | 2017 | 8 |
| Michael | E1128 | Admin | 2020 | 7 |
| Michael | E1128 | Admin | 2019 | 8 |
| Michael | E1128 | Admin | 2018 | 7.5 |
| Michael | E1128 | Admin | 2017 | 6 |
| Michael | E1128 | Admin | 2016 | 7 |
| Frank | E8879 | Finance | 2020 | 5 |
| Frank | E8879 | Finance | 2019 | 6 |
| Frank | E8879 | Finance | 2018 | 6 |
| Frank | E8879 | Finance | 2017 | 7 |
| Jennifer | E5641 | Engineering | 2020 | 8 |
| Jennifer | E5641 | Engineering | 2019 | 8 |
| Jennifer | E5641 | Engineering | 2018 | 8 |
Solution: Please Check the below video
Let us know your queries in the comment section below. We will keep adding more SQL interview questions in this series over time.
