SQL(1/14)

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。

-- 联立两表
-- 每个日期新用户的次日留存数/每个日期登录新用户的个数 即为每个日期新用户留存率
	SELECT one.date,IFNULL(ROUND(two.counts/one.sum,3),0) p FROM
	(
        #每个日期新用户的次日留存数
		SELECT l.date,IFNULL(t.sum,0) sum
		FROM login l
		LEFT JOIN(
			SELECT date,COUNT(user_id) sum
			FROM login l1
			WHERE user_id NOT IN(
				SELECT user_id from login l2
				WHERE l2.date < l1.date
			)
			GROUP BY date
		) t
		ON l.date = t.date
		GROUP BY l.date
		ORDER BY l.date
	)AS one
	JOIN
	(
		#每个日期登录新用户的个数
        SELECT DISTINCT l.date,IFNULL(t.sum,0) counts
		FROM login l
		LEFT JOIN(
			SELECT DATE_SUB(date,INTERVAL 1 DAY) date,count(user_id) sum
			FROM login
			WHERE (user_id,date) in (
				SELECT user_id,DATE_ADD(MIN(date),INTERVAL 1 DAY) date
				FROM login	
				GROUP BY user_id
			)
			GROUP BY date
		)t
		ON l.date = t.date
		ORDER BY l.date
	)two
	ON one.date = two.date
    ORDER BY one.date

牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息

牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。

select name u_n,date,sum(number) over (partition by user_id order by date) ps_num
from passing_number p
inner join user
on user.id=p.user_id
group by date,u_n
order by date,name;
# 使用窗口函数SUM，将日期作为“窗口”计算累加刷题数量

请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)

请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)

select job,round(avg(score),3) avg
from grade
group by job
order by avg desc;

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序

select g1.*
from grade g1
where score > (select avg(score)
              from grade g2
              where g2.job=g1.job
              group by job )
order by g1.id;

请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序

请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序

select id,name,score
from (select g.id,l.name,g.score
      ,dense_rank()over(partition by g.language_id order by score desc) rn
      from grade g
      inner join language l on g.language_id=l.id) t
where rn<=2
order by name,score desc,id;

请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序

请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序

select job,floor((count(job)+1)/2) sta,ceiling((count(job)+1)/2) en
from grade
group by job;

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

select id,job,score,r
from(select *,rank()over(partition by job order by score desc)r,
              count(*)over(partition by job) t
     from grade) A
where round(abs(r-(t+1)/2)*1.0,2)<1
order by id;
select id,job,score,r
from(select *,rank()over(partition by job order by score desc)r,
              count(*)over(partition by job) t
     from grade) A
where round(abs(r-(t+1)/2)*1.0,2)<1
order by id;
# select t.id,t.job,t.score,t.ranking as `rank`
# from
# (select id,job,score,
#     row_number() over(partition by job order by score desc) as ranking
# from grade) as t
# left join 
# (select job,count(score) as total
# from grade
# group by job) as s
# on t.job =s.job
# where (total DIV 2 +1 =ranking) or 
# ((mod(total,2)=0) and (ranking=total DIV 2))
# order by t.id