一份sql笔试

1. 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