全部评论
可以来这里多练习一下 https://www.nowcoder.com/ta/sql
分享
--第一题
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 ;
分享
乐元素
官网直投
相关推荐
点赞 评论 收藏
转发