Practical Exercises to Master MySQL Window Functions and CTEs
Window Functions and CTEs Exercises with solutions [20 exercises with solution]
Explore following practical MySQL queries focused on using window functions and MySQL Common Table Expressions (CTEs) for various data manipulations. Topics include ranking employees by salary, calculating running totals, moving averages, and more. Each exercise comes with a solution, providing hands-on learning for data analysts and database administrators.
1. Rank Employees by Salary
Write a MySQL query to rank employees based on their salary in descending order using a window function.
2. Calculate Running Total of Sales
Write a MySQL query to calculate the running total of sales for each month using a window function.
3. Partition Employees by Department and Rank by Salary
Write a MySQL query to partition employees by department and rank them by salary within each department.
4. Calculate Moving Average of Sales
Write a MySQL query to calculate the 3-month moving average of sales using a window function.
5. Find the difference between Current and Previous Salary
Write a MySQL query to find the difference between the current salary and the previous salary for each employee using a window function.
6. Calculate Cumulative Percentage of Total Sales
Write a MySQL query to calculate the cumulative percentage of total sales for each month using a window function.
7. Find the Top 3 Highest Paid Employees in Each Department
Write a MySQL query to find the top 3 highest paid employees in each department using a window function.
8. Calculate the Salary Difference from Department Average
Write a MySQL query to calculate the difference between each employee's salary and the average salary of their department using a window function.
9. Find the First and Last Sale Date for Each Customer
Write a MySQL query to find the first and last sale date for each customer using a window function.
10. Calculate the Percentage of Total Sales for each Product
Write a MySQL query to calculate the percentage of total sales for each product using a window function.
11. Find the Median Salary in Each Department
Write a MySQL query to find the median salary in each department using a window function.
12. Calculate the Salary Difference from Company Average
Write a MySQL query to calculate the difference between each employee's salary and the average salary of the entire company using a window function.
13. Using Certificates for Database Authentication
Write a MySQL query to find the cumulative distribution of sales using a window function.
14. Calculate the Percentile Rank of Each Employee's Salary
Write a MySQL query to calculate the percentile rank of each employee's salary using a window function.
15. Find the Lead and Lag Values for Sales
Write a MySQL query to find the lead and lag values for sales using a window function.
16. Calculate the Difference between Current and Next Salary
Write a MySQL query to calculate the difference between the current salary and the next salary for each employee using a window function.
17. Calculate the Cumulative Sum of Sales by Quarter
Write a MySQL query to calculate the cumulative sum of sales for each quarter using a window function.
18. Find the Top 5 Highest Sales by Region
Write a MySQL query to find the top 5 highest sales in each region using a window function.
19. Calculate the Average Sales Over a Rolling 3-Month Window
Write a MySQL query to calculate the average sales over a rolling 3-month window using a window function.
20. Find the Employees with the Highest Salary in Each Department
Write a MySQL query to find the employees with the highest salary in each department using a window function.
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics