瀏覽標籤:

Hard

[MySQL][LeetCode][Hard] 185. Department Top Three Salaries

心得

這題是 184. Department Highest Salary 的衍生題,但如果直接套用其方法的話會TimeOut,不過還是記錄一下方法:

問題

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

The Department table holds all departments of the company.

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

答案

  1. 這個方法是在Top Solutions看到的,在WHERE裡面統計有幾個其他同部門的人大於自己的薪水,把小於三個的都列出來,就是答案。
  2. 同上,只是改成WHERE IN效能較佳

     
       

[MySQL][LeetCode][Hard] 262. Trips and Users

心得

這題只是看起來比較多欄位其實沒有多難,題目要求找出除了已經被封鎖的客戶以外的取消率。

問題

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

答案