Leetcode Problem 1635. Hopper Company Queries I

1635. Hopper Company Queries I

Leetcode Solutions

Key approach of the solution

Algorithm

Create a recursive CTE named Months that generates a sequence of numbers from 1 to 12, representing the months of the year.
Create a CTE named Driver that selects driver_id and the month of joining from the Drivers table, filtering for drivers who joined on or before 2020.
Create a CTE named Ride that selects the month and ride_id from the AcceptedRides table joined with the Rides table, filtering for rides requested in 2020.
Perform a LEFT JOIN of the Driver CTE with the Months CTE on the condition that the month of joining is less than or equal to the current month.
Perform a LEFT JOIN of the Ride CTE with the result of the previous join on the month.
Use the COUNT DISTINCT function to calculate the number of active drivers and accepted rides for each month.
Group the results by month and order by month in ascending order.
Select the month, the count of active drivers, and the count of accepted rides in the final output.


erDiagram
    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
    }

    Drivers ||--o{ Rides : requests
    Rides ||--o{ AcceptedRides : accepts

Alternative Approach: Conditional Joins with Sub DataFrames

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 1635. Hopper Company Queries I

Leetcode Solutions

Key approach of the solution

Solution Idea

Query Steps

Code Implementation

Algorithm

Alternative Approach: Conditional Joins with Sub DataFrames

Ask Question

Suggested Answer