关注
--第一题
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 ;
查看原帖
点赞 1
相关推荐
牛客热帖
正在热议
# 牛客帮帮团来啦!有问必答 #
380301次浏览 7610人参与
# 应届生初入职场,求建议 #
21842次浏览 537人参与
# 晒一晒我的offer #
2796159次浏览 49707人参与
# 在国企工作的人,躺平了吗? #
71479次浏览 866人参与
# 简历中的项目经历要怎么写 #
377864次浏览 6356人参与
# 非技术岗薪资爆料 #
6782次浏览 134人参与
# 你更愿意参加线上面试还是线下面试? #
6402次浏览 90人参与
# 非技术薪资爆料 #
63618次浏览 954人参与
# 华为求职进展汇总 #
438259次浏览 4409人参与
# 第一次面试 #
15603次浏览 238人参与
# 租房前辈的忠告 #
20680次浏览 1640人参与
# 应届生应该先就业还是先择业 #
12045次浏览 114人参与
# 安利/避雷我的岗位 #
122152次浏览 2752人参与
# 来聊聊机械薪资天花板是哪家 #
20634次浏览 164人参与
# 机械人怎么评价今年的华为 #
53830次浏览 439人参与
# 谈薪时HR压价该怎么应对 #
32931次浏览 202人参与
# 通信硬件薪资爆料 #
144694次浏览 1075人参与
# 毕业租房也有小确幸 #
19749次浏览 1247人参与
# 数据人offer决赛圈怎么选 #
36565次浏览 658人参与
# 正在实习的你,有转正机会吗? #
83039次浏览 864人参与