--第一题
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 ;