1. 有以下表和字段:
用户表:user,字段:user_id, name, create_time(默认create_time是日期格式)
订单表:order,字段:order_id, user_id, create_time (默认create_time是日期格式)
请写一条SQL语句,做一个报表,包含如下字段:日期,新增用户数,当日订单数,当日下单用户数
select t.create_time,
count(distinct user.user_id) as '新增用户数',
count(distinct order.order_id) as '当日下单数',
count(distinct order.user_id) as '当日下单用户数'
from
(select create_time from user
union #将涉及到的日期去重
select create_time from order
) as t
left join user on user.create_time = t.create_time
left join order on order.create_time = t.create_time
group by t.create_time;
重点:不要简单地通过user id将两个表连接,需要通过时间来判断用户新增,下单情况;
2. 有如下表和字段:
订单表:order, 字段:order_id, user_id, city_id, create_time
请写一条SQL语句,做一个报表,包含如下字段:城市id,最近7天内下过单的用户数,首次下单时间在最近7天内的用户数
# 思路:按照城市获取数据,需要按照城市分组
# 都要求最近7天的数据,可以与curdate() 进行对比
#step1: 查看7天内下过单的用户数
select city_id, count(distinct user_id) as '最近7天内下过单的用户数'
from order
where create_time >=date_add(curdate(),interval -7 day)
group by city_id;
#step2: 查看首次下单时间在7天内的用户ID
select user_id
from orders
group by user_id
having min(create_time)>=date_add(curdate(), interval -7 day)
#step3: 将首次下单时间在7天内的用户ID分城市计数,将结果2加入结果1
select city_id,
count(distinct user_id) as '最近7天内下过单的用户数',
count(distinct if(user_id in (
select user_id from orders
group by user_id
having min(create_time)>=date_add(curdate(), interval -7 day)),user_id, null)
as '首次下单时间在最近7天内的用户数'
from order
where create_time >=date_add(curdate(),interval -7 day)
group by city_id;
重点:主要是在对最近7天内首次下单的用户进行计数,第一要先查询哪些用户在最近7天内首次下单,用最小下单日期与7天内的日期对比;
第二要确认这些用户ID所属的城市,加入条件判断:如果当前城市的用户ID在第一步的结果中,则计数,否则返回空值