leetcode 1635 Hopper公司查询1(postgresql)

需求

表: Drivers

±------------±--------+
| Column Name | Type |
±------------±--------+
| driver_id | int |
| join_date | date |
±------------±--------+
driver_id是该表的主键。
该表的每一行均包含驾驶员的ID以及他们加入Hopper公司的日期。

表: Rides

±-------------±--------+
| Column Name | Type |
±-------------±--------+
| ride_id | int |
| user_id | int |
| requested_at | date |
±-------------±--------+
ride_id是该表的主键。
该表的每一行均包含行程ID(ride_id)，用户ID(user_id)以及该行程的日期(requested_at)。
该表中可能有一些不被接受的乘车请求。

表: AcceptedRides

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| ride_id | int |
| driver_id | int |
| ride_distance | int |
| ride_duration | int |
±--------------±--------+
ride_id是该表的主键。
该表的每一行都包含已接受的行程信息。
表中的行程信息都在“Rides”表中存在。

编写SQL查询以报告2020年每个月的以下统计信息：

截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）。
该月接受的乘车次数（accepted_rides）。
返回按month 升序排列的结果表，其中month 是月份的数字（一月是1，二月是2，依此类推）。

查询结果格式如下例所示。

示例 1：

输入：
表 Drivers:
±----------±-----------+
| driver_id | join_date |
±----------±-----------+
| 10 | 2019-12-10 |
| 8 | 2020-1-13 |
| 5 | 2020-2-16 |
| 7 | 2020-3-8 |
| 4 | 2020-5-17 |
| 1 | 2020-10-24 |
| 6 | 2021-1-5 |
±----------±-----------+
表 Rides:
±--------±--------±-------------+
| ride_id | user_id | requested_at |
±--------±--------±-------------+
| 6 | 75 | 2019-12-9 |
| 1 | 54 | 2020-2-9 |
| 10 | 63 | 2020-3-4 |
| 19 | 39 | 2020-4-6 |
| 3 | 41 | 2020-6-3 |
| 13 | 52 | 2020-6-22 |
| 7 | 69 | 2020-7-16 |
| 17 | 70 | 2020-8-25 |
| 20 | 81 | 2020-11-2 |
| 5 | 57 | 2020-11-9 |
| 2 | 42 | 2020-12-9 |
| 11 | 68 | 2021-1-11 |
| 15 | 32 | 2021-1-17 |
| 12 | 11 | 2021-1-19 |
| 14 | 18 | 2021-1-27 |
±--------±--------±-------------+
表 AcceptedRides:
±--------±----------±--------------±--------------+
| ride_id | driver_id | ride_distance | ride_duration |
±--------±----------±--------------±--------------+
| 10 | 10 | 63 | 38 |
| 13 | 10 | 73 | 96 |
| 7 | 8 | 100 | 28 |
| 17 | 7 | 119 | 68 |
| 20 | 1 | 121 | 92 |
| 5 | 7 | 42 | 101 |
| 2 | 4 | 6 | 38 |
| 11 | 8 | 37 | 43 |
| 15 | 8 | 108 | 82 |
| 12 | 8 | 38 | 34 |
| 14 | 1 | 90 | 74 |
±--------±----------±--------------±--------------+
输出：
±------±---------------±---------------+
| month | active_drivers | accepted_rides |
±------±---------------±---------------+
| 1 | 2 | 0 |
| 2 | 3 | 0 |
| 3 | 4 | 1 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 6 | 5 | 1 |
| 7 | 5 | 1 |
| 8 | 5 | 1 |
| 9 | 5 | 0 |
| 10 | 6 | 0 |
| 11 | 6 | 2 |
| 12 | 6 | 1 |
±------±---------------±---------------+
解释：
截至1月底->两个活跃的驾驶员（10,8），没有被接受的行程。
截至2月底->三个活跃的驾驶员（10,8,5），没有被接受的行程。
截至3月底->四个活跃的驾驶员（10,8,5,7），一个被接受的行程（10）。
截至4月底->四个活跃的驾驶员（10,8,5,7），没有被接受的行程。
截至5月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。
截至6月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（13）。
截至7月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（7）。
截至8月底->五个活跃的驾驶员（10,8,5,7,4），一位接受的行程（17）。
截至9月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。
截至10月底->六个活跃的驾驶员（10,8,5,7,4,1），没有被接受的行程。
截至11月底->六个活跃的驾驶员（10,8,5,7,4,1），两个被接受的行程（20,5）。
截至12月底->六个活跃的驾驶员（10,8,5,7,4,1），一个被接受的行程（2）。

输入

输出

with t1 as (
SELECT to_char(generate_series(timestamp '2020-01-01', timestamp '2020-12-01', interval '1 month'), 'YYYY-MM') AS month
),t2 as (
select *,to_char(join_date,'YYYY-MM') as date1,
       sum(1) over (rows between unbounded preceding and current row ) as rn1
from Drivers
),t3 as (
select month,max(rn1) over (rows between unbounded preceding and current row ) as active_drivers
from t1
left join t2
on t1.month=t2.date1
),t4 as (
select to_char(requested_at,'YYYY-MM') as date2,coalesce(a.ride_id,0) as aid
from rides r
left join acceptedrides a
on r.ride_id=a.ride_id
order by requested_at
),t5 as (
select date2,count(date2) as cnt
from t4
where aid>0
group by date2
),t6 as (
select month,coalesce(cnt,0) as accepted_rides
from t1
left join t5
on t1.month=t5.date2
)
select substring(t3.month,6,7) as month,active_drivers,accepted_rides
from t3
left join t6
on t3.month=t6.month
order by month;