--第一题 select coalesce(a.create_date, b.create_date) as static_date , coalesce(a.new_user_cnt, 0) as new_user_cnt , coalesce(b.order_cnt, 0) as order_cnt , coalesce(b.order_user_cnt, 0) as order_user_cnt from ( select substr(create_time, 1, 10) as create_date , count(user_id) as new_user_cnt from user where 1 = 1 group by substr(create_time, 1, 10) ) a full join ( select substr(create_time, 1, 10) as create_date , count(distinct user_id) as order_user_cnt , count(order_id) as order_cnt from order where 1 = 1 group by substr(create_time, 1, 10) ) b on a.create_date = b.create_date ; --第二题 select city_id , count(user_id) as order_user_cnt , count(if(to_date(first_order_date) between between date_sub(current_date, 7) and date_sub(current_date, 1), user_id, null)) as first_order_user_cnt from ( select user_id , city_id from order where to_date(substr(create_time, 1, 10)) between date_sub(current_date, 7) and date_sub(current_date, 1) group by user_id , city_id ) a join ( select user_id , city_id , substr(min(create_time), 1, 10) as first_order_date from order where 1 = 1 group by user_id , city_id ) b on a.user_id = b.user_id and a.city_id = b.city_id where 1 = 1 group by city_id ;