Hopper Company Queries I — LeetCode 1635 Python Solution

Table: Drivers +-------------+---------+ | Column Name | Type | +-------------+---------+ | driver_id | int | | join_date | date | +-------------+---------+ driver_id is the primary key (column with unique values) for this table. Each row of this table contains the driver's ID and the date they joined the Hopper company.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| join_date   | date    |
+-------------+---------+
driver_id is the primary key (column with unique values) for this table.
Each row of this table contains the driver's ID and the date they joined the Hopper company.

import duckdb
import pandas as pd

def solution(drivers: pd.DataFrame, rides: pd.DataFrame, accepted_rides: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Drivers", drivers)
    con.register("Rides", rides)
    con.register("AcceptedRides", accepted_rides)
    return con.execute("""WITH RECURSIVE
    Months AS (
        SELECT
            1 AS month
        UNION ALL
        SELECT
            month + 1
        FROM Months
        WHERE month < 12
    ),
    Ride AS (
        SELECT MONTH(requested_at) AS month, COUNT(1) AS cnt
        FROM
            Rides AS r
            JOIN AcceptedRides AS a ON r.ride_id = a.ride_id AND YEAR(requested_at) = 2020
        GROUP BY month
    )
SELECT
    m.month,
    COUNT(driver_id) AS active_drivers,
    IFNULL(r.cnt, 0) AS accepted_rides
FROM
    Months AS m
    LEFT JOIN Drivers AS d
        ON (m.month >= MONTH(d.join_date) AND YEAR(d.join_date) = 2020) OR YEAR(d.join_date) < 2020
    LEFT JOIN Ride AS r ON m.month = r.month
GROUP BY month;""").df()