多说几句，JAVA真不行了？

刚刚看到那个tiobe最新的指数，JAVA只剩下8.84%？感觉上次看的时候还有百分之十呢。

185-department-top-three-salaries

https://leetcode.com/problems/department-top-three-salaries/description/

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

编写解决方案，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

dense_rank，部门工资前三高的所有员工，

-- 基本语句
select d.name as 'Department', e1.name as 'Employee', e1.salary as 'Salary'
from Employee e1
join Department d on e1.departmentId = d.id
where 3 > (select count(distinct e2.salary)from Employee e2where e2.salary > e1.salary and e1.departmentId = e2.departmentId);
-- 窗口函数 DENSE_RANKwith employee_department as(
select d.id,d.name as 'Department',e.name as 'Employee',salary as 'Salary',dense_rank() over (partition by d.id order by salary desc ) as rnkfrom Department d join Employee e on d.id = e.departmentId
)
select Department, Employee, Salary
from employee_department
where rnk <= 3

196-delete-duplicate-emails

https://leetcode.com/problems/delete-duplicate-emails/description/
编写解决方案 删除 所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。

（对于 SQL 用户，请注意你应该编写一个 DELETE 语句而不是 SELECT 语句。）

（对于 Pandas 用户，请注意你应该直接修改 Person 表。）

运行脚本后，显示的答案是 Person 表。驱动程序将首先编译并运行您的代码片段，然后再显示 Person 表。Person 表的最终顺序 无关紧要 。

删除重复的电子邮箱，

delete p1 from Person p1, Person p2
where p1.Email = p2.Email and p1.Id > p2.Id

197-rising-temperature

https://leetcode.com/problems/rising-temperature/description/

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 无顺序要求 。

上升的温度，

-- left join
select w1.id
from Weather w1
join Weather w2
on datediff(w1.recordDate, w2.recordDate) = 1
where w1.temperature > w2.temperature

262-trips-and-users

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

行程和用户，hard

# 直接连
select request_at as day,round(sum(status != 'completed') / count(*), 2) as 'Cancellation Rate'
#        ,count(*)
from Trips
left join Users as c on Trips.client_id = c.users_id
left join Users as d on Trips.driver_id = d.users_id
where c.banned = 'No' and d.banned = 'No' and request_at between '2013-10-01' AND '2013-10-03'
group by day# Using Subqueries子查询
SELECTrequest_at AS Day,ROUND(SUM(status != 'completed') / COUNT(status), 2) AS 'Cancellation Rate'
FROMTrips
WHERErequest_at BETWEEN '2013-10-01' AND '2013-10-03'AND client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')AND client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
GROUP BYDay;# CTE 公用表表达式
with TripStatus as (select request_at as Day,Trips.status != 'completed' as cancelledfrom Tripsjoin Users as c on Trips.client_id = c.users_id and c.banned = 'No'join Users as d on Trips.driver_id = d.users_id and d.banned = 'No'where request_at between '2013-10-01' AND '2013-10-03'
)
select Day,round(sum(cancelled) / count(cancelled), 2) AS 'Cancellation Rate'
from TripStatus
group by Day

511-game-play-analysis-i

https://leetcode.com/problems/game-play-analysis-i/description/

first_value，2025年6月10日 星期二

# 分组并提取最小值
select player_id, min(event_date) as 'first_login'
from Activity
group by player_id# 窗口函数
# 可以在内联视图中使用 RANK()、DENSE_RANK() 或 ROW_NUMBER() 与之结合使用。选择不会影响结果，因为 (player_id, event_date) 是 Activity 表的主键（即我们不必担心有多个行具有 rnk 值为 1 的可能性，因为分区是由 player_id 创建的，行是按 event_date 排序的，从而保证了唯一的 rnk 值）：
select distinct a.player_id,first_value(a.event_date) over (partition by a.player_idorder by a.event_date) as 'first_login'
from Activity a

550-game-play-analysis-iv

https://leetcode.com/problems/game-play-analysis-iv/description/

连续两天登录的比率，date_sub

# 编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
# Subqueries and multi-value use of the IN comparison operator
select round(count(a1.player_id) / (select count(distinct a3.player_id)from Activity a3), 2) as fraction
from Activity a1
where (a1.player_id, date_sub(a1.event_date, interval 1 day )) in(select a2.player_id, min(a2.event_date)from Activity a2group by a2.player_id);
# CTEs and INNER JOIN
with first_logins as (select a.player_id, min(a.event_date) as first_loginfrom Activity agroup by a.player_id
), consec_logins as (select count(a.player_id) as num_loginsfrom first_logins finner join Activity a on f.player_id = a.player_idand f.first_login = date_sub(a.event_date, interval 1 day )
)
select round((select num_logins from consec_logins) /(select count(player_id) from first_logins), 2
) as fraction