SQL题目

• 了解table内容 (PK, FK, columns and meanings? ERD?)
• 理解需求/问题
• 想执行顺序

https://www.nowcoder.com/ta/sql

=======

次日留存率问题

牛客网SQL68

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

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;

===等价于===

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的：https://www.1keydata.com/tw/sql/sql-rank.html

累计总计

use window function:

select t.name, t.sales, sum(t.sales) over (order by t.sales desc) from total_sales t;

without window function:

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;

Median

#569 求中位数

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，应该是，例如：

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