多說幾句，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