Leetcode Problem 1651. Hopper Company Queries III

1651. Hopper Company Queries III

Leetcode Solutions

Calculating Rolling Averages with Window Functions

Generate a series of months for the year 2020 using a recursive common table expression (CTE).
Join the Rides table with the AcceptedRides table to get the total ride_distance and ride_duration for each month.
Use a left join to combine the generated months with the aggregated ride information, ensuring that all months are represented, even if there are no rides in a given month.
Use the window function AVG() with the ROWS BETWEEN clause to calculate the rolling average over the current and next two months.
Round the results to two decimal places and filter out the months that do not form a complete 3-month window.

erDiagram
    DRIVERS ||--o{ RIDES : has
    RIDES ||--o{ ACCEPTEDRIDES : has
    DRIVERS {
        int driver_id PK
        date join_date
    }
    RIDES {
        int ride_id PK
        int user_id
        date requested_at
    }
    ACCEPTEDRIDES {
        int ride_id PK
        int driver_id
        int ride_distance
        int ride_duration
    }

Calculating Rolling Averages with Self-Joins

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 1651. Hopper Company Queries III

Leetcode Solutions

Calculating Rolling Averages with Window Functions

Solution Idea

Query Steps

Code Implementation

Calculating Rolling Averages with Self-Joins

Ask Question

Suggested Answer