SQL小题

 

查找入职员工时间排名倒数第三的员工所有信息

select emp_no,birth_date,first_name,last_name,gender,hire_date
from(
select *,
dense_rank() over(order by hire_date desc )as n
from employees
) a
where n=3

 

 

删除emp_no重复的记录，只保留最小的id对应的记录。

delete from titles_test where id in (
select id from titles_test where id not in
(select min(id) from titles_test group by emp_no)
)

 

最近的登录日期

with
ect1 as(
　　select a.id,b.name as u,c.name as c,a.date
　　from login a
　　left join user b on a.user_id=b.id
　　left join client c on a.client_id=c.id),
ect2 as(
　　select u,c,date
　　from ect1 where id in
　　　　(select max(id) from login group by user_id))
select * from ect2 order by u asc

 

 

次日成功的留存率

 

with
ect1(num1) as
　　(select count(1) as num1 from login a,(
　　select user_id,date_add(min(date),interval 1 day) as date
　　from login
　　group by user_id)b
　　where a.user_id=b.user_id
　　and a.date=b.date),
ect2 as
　　(select count(distinct user_id) as num2 from login)
select cast(ect1.num1 as float)/cast(ect2.num2 as float) from ect2,ect1

 

 

每个日期登录新用户个数

 

select distinct login.date,count(t.user_id) from login
　　left join(
　　　　select a.user_id,min(a.date) as date
　　　　from login a group by a.user_id)t
　　on login.date=t.date and login.user_id=t.user_id
group by login.date
order by login.date asc