leetcode练习题2

Leetcode练习题

262. 行程和用户

https://leetcode-cn.com/problems/trips-and-users/

法一:多次关联

取消率 = 每组的取消的行程数 / 每组的总行程数

SELECT T.request_at AS 'Day',
	ROUND( SUM(IF(T.STATUS = 'completed', 0, 1))/COUNT(T.STATUS),2) AS 'Cancellation Rate'
	FROM Trips AS T
	JOIN Users AS U1 ON (T.client_id = U1.user_id AND U1.banned = 'No')
	JOIN Users AS U2 ON (T.driver_id = U2.user_id AND U2.banned = 'No')
	WHERE T.request_at Between '2013-10-01' AND '2013-10-03'
	GROUP BY T.request_at;

其中 SUM 求和函数，COUNT 计数函数，ROUND 四舍五入函数。

法二:用subquery的方法

SELECT T.request_at AS 'Day',
	ROUND( SUM(IF(T.STATUS = 'completed', 0, 1))/COUNT(T.STATUS),2) AS 'Cancellation Rate'
	FROM Trips AS T
	WHERE T.Client_Id NOT IN (
	SELECT user_id
	FROM users
	WHERE banned = 'Yes' )
	AND 
	T.Driver_Id NOT IN (
	SELECT user_id
	FROM users
	WHERE banned = 'Yes')
	AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
	GROUP BY T.request_at;

182. 查找重复的电子邮箱

https://leetcode-cn.com/problems/duplicate-emails/

法一:使用 GROUP BY 和临时表

select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;

法二：使用 GROUP BY 和 HAVING 条件 （记住having 这个条件）

错误答案

select Email
from Person
group by Email
having count(Email) > 1;

196. 删除重复的电子邮箱

我写的答案

select  min(Id) as ID , Email 
from Person
group by Email;

正确答案

法一:delete语法如何作用于联结表，inner join的原理

(看清题目意思）

delete a
from person as a inner join person as b
where a.email = b.email and a.id > b.id

法二:delete语法如何作用于联结表，inner join的原理

delete 
from person
where Id in 
(select Id 
from (select Id, row_number() over (partition by Email order by Id) rn from Person) t1
where rn >1)

sql的执行顺序是from–where–group by–having–select–order by

日期函数

记得case 里面的判断条件是 mod(1,1) = 0

留存率

1097. 游戏玩法分析 V

法一：with table

with t1 as (select player_id, event_date, row_number() over (partition by player_id order by event_date) as rk
from Activity
)
select a.event_date as install_dt, 
round(count(distinct a.player_id ),2) as installs,
round(count(distinct b.player_id)/count(distinct a.player_id),2) as Day1_retention
from t1 a 
left join t1 b 
on a.player_id = b.player_id and datediff(a.event_date,b.event_date) = -1
where a.rk = 1
group by a.event_date
order by  a.event_date;

法二：用if 函数

select first_date as install_dt,
count(distinct player_id) as installs,
round(sum(if(datediff(event_date, first_date) = 1,1,0))/
count(distinct player_id),2) as Day1_retention
from 
(select player_id, event_date, min(event_date) over (partition by player_id) as first_date from activity) tmp
group by first_date;

先利用min() over()窗口函数求出每个player_id的注册日期 然后按注册日期分组，求与注册日期差值为1的数量。 通用性很强，可以求3,7，30日的留存率，只需把差值改为对应的天数即可。

550. 游戏玩法分析 IV

https://leetcode-cn.com/problems/game-play-analysis-iv/
思路：
分析本题，让求出首次登陆游戏后第二天又登陆游戏的玩家所占的比例，保留两位小数；
首先我们可以把问题拆分，先求出所有玩家第一次登陆的数据，然后把这个作为临时表，然后我们把所有数据和他们第一次登陆的数据进行比较，如果日期相差1天，即为符合条件的玩家，用这部分的玩家数量除以所有玩家的数量，然后保留两位小数就可以得出结果了；解法有多种，但是思路都是一样的，选择最直观效率的方法完成即可

法一：用min 函数+ case when + datediff

select round(sum(case when datediff(a.event_date,b.first_date) = 1 then 1 else 0 end)/(select count(distinct player_id) from activity),2) as fraction
from activity a
left join 
(select player_id, min(event_date) first_date from activity 
group by player_id) b
on a.player_id = b.player_id;

法二：用min 函数+ case when + datediff

select rount(sum(if(a.event_date is not null,1,0))/count(*),2) as fraction
from
(select player_id, min(event_date) as login
from activity
group by player_id) p
left join activity a
on p.player_id = a.player_id and datediff(a.event_date, p.login)=1;

总结各类表格格式化问题

认认真真看这篇文章！！以及这篇文章！！总结了各种技巧和套路，给我去看！
https://leetcode-cn.com/problems/students-report-by-geography/solution/zong-jie-ge-lei-biao-ge-ge-shi-hua-wen-t-tl4e/

https://leetcode-cn.com/problems/reformat-department-table/solution/group-byben-zhi-lun-by-loverxp-7mgy/