sql学习，刷题4

现有员工表employees如下：

有员工奖金表emp_bonus:

有薪水表salaries:

其中bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 to_date='9999-01-01’表示当前薪水。
请你给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
bonus结果保留一位小数，输出结果按emp_no升序排序。
以上数据集的输出结果如下

本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时，得到 salary * 0.1；当 btype = 2 时，得到 salary * 0.2；其他情况得到 salary * 0.3。详细用法请参考：
http://www.sqlite.org/lang_expr.html 中的【The CASE expression】
http://www.2cto.com/database/201202/120267.html 中的【条件表达式】

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype 
 WHEN 1 THEN s.salary * 0.1
 WHEN 2 THEN s.salary * 0.2
 ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

其实观察测试数据会发现 btype 只有1,2,3三种情况，即使不会 CASE 表达式，也能运用四则运算解出：（注意要除以10.0，如果除以10的话，结果的小数位会被舍去）

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(s.salary * b.btype / 10.0) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

有一个薪水表salaries简况如下:

对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列，且按照emp_no升序排列：

这题重点技巧如何计算出【1，2，2，3】这种不跳数字的排序

select t1.emp_no, t1.salary, count( distinct t2.salary) as t_rank
from salaries t1,salaries t2 
where t1.salary <= t2.salary
group by t1.emp_no,t1.salary
order by t1.salary desc, t1.emp_no

t1.salary <= t2.salary找出在查询t1.salary的时候有多少个t2.salary大于等于t1.salary。这里需要用distinct对t2.salary去重,不然出现的是【1，3，3，4】这种跳数字的排序。因为我们使用了聚合函数count()如果不加group by t1.emp_no的话，只会返回一条结果。同时goup by t1.salary也应该加上，因为salary属于列的字节名，不是主键且不唯一。而select子句只能存在常数、聚合函数、group by子句指定列(聚合键)。所以这里应该填上group by t1.salary。否则可能会出现记录不匹配的情况

select emp_no,
       salary,
       dense_rank() over(order by salary desc) as t_rank
from salaries

DENSE_RANK()是一个窗口函数，它为分区或结果集中的每一行分配排名，而排名值没有间隙。
行的等级从行前的不同等级值的数量增加1。

引用看到其他小伙伴的贡献：
1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······

牛客每天有很多用户刷题，发帖，点赞，点踩等等，这些都会记录相应的积分。
有一个用户表(user)，简况如下

还有一个积分表(grade_info)，简况如下:

第1行表示，user_id为1的用户积分增加了3分。
第2行表示，user_id为2的用户积分增加了3分。
第3行表示，user_id为1的用户积分减少了1分。
…
最后1行表示，user_id为3的用户积分减少了1分。
请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序，以上例子查询结果如下

1、创建t1表，取出user_id，和通过if添加正负号的分数grade_num。
2、分组求和得到user_id及对应的总分数grade_sum，并通过WITH……AS……打包成临时表t，方便后续多次引用
3、WHERE筛选出grade_sum=最大值的行，连接并取出题目所需列

WITH t AS (
    SELECT  user_id,SUM(grade_num) AS grade_sum
    FROM (SELECT user_id,grade_num * IF(type= 'add',1,-1) AS grade_num
          FROM grade_info )AS t1
    GROUP BY user_id )
SELECT id,name,grade_sum
FROM t
JOIN user u ON u.id = t.user_id
WHERE grade_sum  = (SELECT MAX(grade_sum) FROM t)

SELECT u.id, u.name, g.grade_sum
FROM user u,(
    select user_id, sum(if(type='add',grade_num,-grade_num)) grade_sum,
    dense_rank() over (order by sum(if(type='add',grade_num,-grade_num)) desc) rk
    from grade_info
    group by user_id) g
WHERE u.id=g.user_id and g.rk=1
ORDER BY u.id;

有一个，部门关系表dept_emp简况如下:

有一个部门经理表dept_manager简况如下:

有一个薪水表salaries简况如下:

获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
以上例子输出如下:.

本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：

1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem

2、再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录sdm

3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary >
sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary

SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, 
sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no 
      FROM salaries s INNER JOIN dept_emp de 
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem, 
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s 
 INNER JOIN dept_manager dm 
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(login)记录表，简况如下

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户
请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下

在每一个date分组中，有数条记录
这些记录中，只有满足用户的date是首次（min）的，才被记录

select date,sum(case 
when (user_id,date) in (select user_id,min(date) from login group by user_id)
               then 1
               else 0
               end) as new
from login
group by date
order by date

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。
有一个订单信息表(order_info)，简况如下

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。
最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了C++课程的订单，状态为购买成功。
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序，以上例子查询结果如

select t.id,t.user_id,t.product_name,t.status,t.client_id,t.date
from
(select *, count(*) over(partition by user_id) as num
from order_info
where product_name in ('C++','Java','Python')
and status='completed'
and date >'2025-10-15')t
where t.num>=2
order by t.id

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的非拼团(is_group_buy为No)订单，但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的非拼团(is_group_buy为No)订单，状态为购买成功。
最后1行表示user_id为557336的用户在2025-10-25的时候使用了下了C++课程的拼团(is_group_buy为Yes)订单，拼团不统计客户端，所以client_id所以为0，状态为购买成功。
有一个客户端表(client)，简况如下

请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序，以上例子查询结果如下

解析:

id为4，6的订单满足以上条件，且因为4是通过IOS下单的非拼团订单，则记: IOS 1，6是通过PC下单的非拼团订单，则记: PC 1;id为5，7的订单满足以上条件，且因为5与7都是拼团订单，则记: GroupBuy 2;最后按照source升序排序

select 
    if(name is null,'GroupBuy',name) as source,count(1) as cnt 
        ##  修改null值为GroupBuy
from(
    select  
        user_id,is_group_buy,name,count(user_id) over(partition by user_id) as cnt 
    FROM 
        `order_info` 
    left join client  
    on 
        order_info.client_id = client.id
    where 
        date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')
) test 
    where cnt>1     
        ## 筛选出大于等于2的
    group by name
        ## 分组
    order by source
        ## 排序

牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
第4行表示user_id为2的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
最后1行表示user_id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下

(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5
mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函数为round

select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

留存率 = 第一天且下一天也登陆的用户数 / 总用户数
总用户数

SELECT COUNT(DISTINCT user_id) FROM login

第一天且下一天也登陆的用户数

SELECT COUNT(DISTINCT user_id)
FROM login
WHERE (user_id, date) in (
  select user_id, DATE_ADD(min(date), INTERVAL 1 DAY)
  from login
  GROUP by user_id
)

汇总两部分代码，计算留存率

SELECT ROUND(COUNT(DISTINCT user_id) * 1.0 / (SELECT COUNT(DISTINCT user_id) FROM login), 3) as p
FROM login
WHERE (user_id, date) in (
  select user_id, DATE_ADD(min(date), INTERVAL 1 DAY)
  from login
  GROUP by user_id
)