1、
select substr(time,1,10),count(order_id),count(distinct passenger_id)
from order
where substr(time,1,7)='2023-08'
group by substr(time,1,10)
order by substr(time,1,10);

2、
select city_id
from
(select * from order where substr(time,1,7) = '2022-08') t1
left join
(select * from passenger where sex='女') t2
on t1.passenger_id = t2.passenger_id
where t2.passenger_id is not null
group by city_id
order by count(1) desc
limit 10;

3、---第三题的count(xxx)需要好好理解，count(1)是主表总数，count(t2.xxx)则已经去除了NULL，因为count不统计NULL值
select count(t2.passenger_id)/count(1) ratio,
from
(select * from passenger where substr(time_new,1,7) = '2022-08') t1 --- 8月首发的passenger_id
left join
(select passenger_id from order where substr(time,1,7) = '2022-09' group by passenger_id) t2  ---9月发过单的passenger_id
on t1.passenger_id = t2.passenger_id