nowcoder SQL

—> 中位数

—> 查找每个公司薪水中位数的员工

数据&建表

create table employee
(
    id      int,
    company varchar(11),
    salary  int
);

insert into employee (id, company, salary)
values (1, 'A', 2341),
       (2, 'A', 341),
       (3, 'A', 15),
       (4, 'A', 15314),
       (5, 'A', 451),
       (6, 'A', 513),
       (7, 'B', 15),
       (8, 'B', 13),
       (9, 'B', 1154),
       (10, 'B', 1345),
       (11, 'B', 1221),
       (12, 'B', 234),
       (13, 'C', 2345),
       (14, 'C', 2645),
       (15, 'C', 2645),
       (16, 'C', 2652),
       (17, 'C', 65);

原表 employee

id	company	salary
1	A	2341
2	A	341
3	A	15
4	A	15314
5	A	451
6	A	513
7	B	15
8	B	13
9	B	1154
10	B	1345
11	B	1221
12	B	234
13	C	2345
14	C	2645
15	C	2645
16	C	2652
17	C	65

需求：查找每个公司薪水中位数的员工

代码编写

select *
from (
         select id,
                salary,
                row_number() over (partition by company order by salary) as rn,
                count(1) over (partition by company)                     as cnt
         from employee
     ) t1
where rn in (floor((cnt + 1) / 2), floor((cnt + 2) / 2))

id	company	salary
5	A	451
6	A	513
12	B	234
9	B	1154
14	C	2645

—> 牛客直播课分析

—> 牛客直播各科目出勤率

链接

with a as (
    select course_id,
           course_name
    from course_tb
),
     b as (
         select course_id,
                count(distinct if(timestampdiff(minute, in_datetime, out_datetime) >= 10, user_id, null)) as flag
         from attend_tb
         group by course_id
     ),
     c as (
         select course_id,
                sum(if(if_sign = 1, 1, 0)) as cnt
         from behavior_tb
         group by course_id
     )
select a.course_id,
       a.course_name,
       round(b.flag * 100 / c.cnt, 2) as rate
from a
         join b on a.course_id = b.course_id
         join c on a.course_id = c.course_id
order by a.course_id

—> 某音短视频

—> 近一个月发布的视频中热度最高的top3视频

链接