数据分析SQL面试题目9套-第6题（笔记）

题目来源：https://www.jianshu.com/p/0f165dcf9525

1. 题目

建表语句：

create table userlog
(
    id int ,
    name varchar(10),
    EmailAddress varchar(50),
    lastlogon datetime
);

insert into userlog values(100,'test4','test4@yahoo.cn','2007-11-25 16:31:26');
insert into userlog values(13,'test1','test4@yahoo.cn','2007-3-22 16:27:07');
insert into userlog values(19,'test1','test4@yahoo.cn','2007-10-25 14:13:46');
insert into userlog values(42,'test1','test4@yahoo.cn','2007-10-25 14:20:10');
insert into userlog values(45,'test2','test4@yahoo.cn','2007-4-25 14:17:39');
insert into userlog values(49,'test2','test4@yahoo.cn','2007-5-25 14:22:36');

2. 解题

2.1 查询出每个用户最近一次登录的记录(每个用户只显示一条最近登录的记录)，以及给出每个用户的登录总次数(同一天多次登录人为是一次)

select name,
       max(lastlogon) as 'latest_log',
       count(distinct date_format(lastlogon, '%Y-%m-%d')) as 'sum_log'
from userlog
group by name;

2.2 生成一张临时表，表名自定，四列数据，分别是：

name, last logon, num_logontime, num_logonday
num_logontime (要求：按时间给出每个人的登录次数，登录时间最早的为1，之后分别为2, 3, 4等)
num_logonday (要求：按天数给出每个人的登录次数，同一天多次登录人为是同一次，最早的一次标记为1，之后的依次类推)

create table new_table as
select name,
       lastlogon,
       row_number() over (partition by name order by lastlogon) 'num_logontime',
       rank() over (partition by name order by date_format(lastlogon, '%Y-%m-%d')) 'num_lohonday'
from userlog;