Window function: rank over date

Question

Use window functions to compute rank partitioned by date.

Codemia · Accepted Answer

The data involved in this problem includes a table, let's call it `rides`, which contains information about ride-sharing trips, including a `ride_id`, `pickup_date`, and `driver_rating`. The objective is to compute the rank of drivers based on their ratings for each date. This means that for each distinct `pickup_date`, we need to assign a rank to drivers based on their `driver_rating`, such that the highest rating gets the top rank (1) for that day.

1. **Identify the Relevant Data**: We'll need the `pickup_date` and `driver_rating` from the `rides` table.  
2. **Create a CTE**: We can start by writing a Common Table Expression (CTE) to simplify our query structure and improve readability.  
3. **Use Window Function**: Within the CTE, we'll employ the `RANK()` window function, partitioned by `pickup_date` and ordered by `driver_rating` in descending order.  
4. **Select and Return Results**: Finally, we will select the relevant columns from the CTE to display the date, driver rating, and their corresponding rank.

Here is the SQL query that accomplishes the task:
```sql
WITH RankedRides AS (
    SELECT 
        pickup_date, 
        driver_rating, 
        RANK() OVER (PARTITION BY pickup_date ORDER BY driver_rating DESC) AS driver_rank
    FROM rides
    WHERE driver_rating IS NOT NULL  
)
SELECT * FROM RankedRides;
```  
This query first creates a CTE called `RankedRides`, which computes the rank of each driver's rating for each pickup date. It then selects all fields from the CTE.

To optimize this query for large datasets, consider the following strategies:  
1. **Indexing**: Create an index on the `pickup_date` and `driver_rating` columns to speed up the sorting and partitioning operations.  
2. **Filter Early**: Use `WHERE` clauses to filter out unnecessary rows before the ranking operation (like excluding NULL ratings), which reduces the dataset size.  
3. **Analyze Execution Plan**: Use the database's execution plan to identify bottlenecks and ensure the query is leveraging the indexes effectively.  
4. **Batch Processing**: If the dataset is extremely large, consider processing the data in smaller batches rather than all at once.

Several edge cases need to be considered in this query:  
1. **NULL Driver Ratings**: The query already handles NULL ratings by excluding them in the `WHERE` clause, ensuring we only rank valid ratings.  
2. **Duplicates**: If multiple drivers have the same rating on the same date, the `RANK()` function will assign the same rank to all of them, which is desired behavior.  
3. **No Rides on a Date**: If there are no rides on a particular date, that date will simply not appear in the results, which is acceptable.  
4. **Outliers**: If there are extremely high or low ratings, ensure that these do not affect the ranking of other drivers disproportionately, although the ranking function handles this naturally.

Window function: rank over date

Lyft

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Problem Understanding

Approach

Submit Your Answer

Lyft Data Scientist Interview Guide

Related Questions

Optimize a slow query on products

Calculate conversion rate per user from orders

Window function: rank over sessions

Optimize a slow query on orders

Write a query to find time to first action from sessions