0%

SQL题目

留存率

牛客网SQL68

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

牛客网SQL70

1
2
3
4
5
6
7
8
9
10
11
12
13
select (select subdate(t2.login_date, 1)) as date,
round(t2.cnt/(select count(user_id) from login where date=(select subdate(t2.login_date, 1)) and (user_id, (select subdate(t2.login_date, 1))) in (select user_id, min(date) as day1 from login group by user_id) group by date), 3) as p from
(select t.login_date, count(t.2ndlogin_diff) cnt from
(select user_id, date as login_date, datediff(nth_value(date, 2) over(partition by user_id order by date),nth_value(date, 1) over(partition by user_id order by date)) as 2ndlogin_diff
from login order by date) t
where t.2ndlogin_diff=1
group by t.login_date
) t2
union
select date, 0.000 p from login where date not in (select min(date) as day1 from login group by user_id)
union
select l.date, 0.000 p from login l where (select date_add(l.date, interval 1 day)) not in (select date from login)
order by date;

OR

1
2
3
4
5
6
7
8
9
10
11
select date
,ifnull(round((sum(case when (user_id,date)in
(select user_id,date_add(date,interval -1 day)
from login group by user_id)
then 1 else 0 end))/
(sum(case when (user_id,date)in
(select user_id,min(date)from login group by user_id)
then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

排序

  • 用window function;
  • 不用window function的:SQL排序

累计总计

  • window function:
    1
    select t.name, t.sales, sum(t.sales) over (order by t.sales desc) from total_sales t;
  • without window function
    1
    2
    3
    4
    5
    SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
    FROM Total_Sales a1, Total_Sales a2
    WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name)
    GROUP BY a1.Name, a1.Sales
    ORDER BY a1.Sales DESC, a1.Name DESC;

    中位数

    LeetCode 569
    1
    2
    select id, company, salary from
    (select id, company, salary, cast(row_number() over(partition by company order by salary asc, id asc) as signed) as 'id1', cast(row_number() over(partition by company order by salary desc, id desc) as signed) as 'id2' from employee) as newtable where abs(id1-id2)=1 or id1=id2;

##其它题目
LeetCode:

  • 180 连续3次某列相同的row?
  • 196 update/delete…改变一个table时就不能inner reference这个table,应该是,例如:
    1
    2
    delete from Person
    where Id not in (select min(Id) from (select * from Person) p group by Email)
  • 197 用self join要比select where in快!
    window function frame
  • 579
    CTE solve ‘variable’ problem
  • 1645