SQL Window Function Interview Question: Ranking Problem

SQL is the most important skill for a data science/analytics professional and during the interviews, SQL window function problems are always the interviewer’s favorite. 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()

The OVER() clause differentiates the window functions from other functions in SQL. The OVER() clause defines the window of rows over which the window function is applied.

Now, let’s test yourself with a real SQL interview question.

Problem Statement: Find top 2 users with highest total order amount for each company

Write an SQL query to return top 2 users from each company that have the maximum total order amount (sum of all orders by user). Output should contain Company, User_Id and rank of the user. In case there are multiple users with the same rank then keep them all.

SQL Tables: Orders, Company