交易表结构为user_id(用户ID),order_id(订单ID),pay_time(付款时间),order_amount(金额)
-
写sql查询过去一个月付款用户量(提示:用户量需去重)最高的三天分别是哪几天?
-
写sql查询昨天每个用户最后付款的订单ID及金额
思路:求最高的三天,肯定是先排序,后limit. 先求出每天的付款用户量,既然每天,那肯定要按天分组了;按照题目要求过滤条件有:1.过去一个月 2.付款用户(即要排除未付款的用户),另外求用户量需要去重,题目中也有提示,因为存在同一个用户每天有多笔消费记录的情况;返回排在前三的付款用户量及对应的时间(天)
-- 1 select date_format(pay_time,"%Y-%m-%d") days, count(distinct user_id) from table where pay_time=date_sub(now(),interval 1 month) group by date_format(pay_time,"Y%-%m-%d") order by count(distinct user_id) desc limit 3;
-- 2 select a.user_id,a.order_amount from ( select user_id,order_amount, row_number() over(partition by user_id order by pay_time desc) as rank from table where date_format(pay_time,"%Y-%m-%d")=date_sub(curdate(),interval 1 day) as a ) where rank=1;
用户登录日志表为user_id,log_id,session_id,visit_time
用sql查询近30天每天平均登录用户数量
select avg(numUser) as averageUsers from ( select date_format(visit_time,"%Y-%m-%d") as visit_date, count(distinct user_id) as numUser from table where date_diff(curdate(),visit_date)<=30 group by visit_date );