數據科學家 算法工程師 面試準備 全套-github.com/LongxingTan/Machine-learning-interview
1050. 合作過至少三次的演員和導演
SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3;
1076. Project Employees II
SELECT TOP 1 WITH TIES project_id
FROM Project
GROUP BY project_id
ORDER BY COUNT(employee_id) DESC;
1082. Sales Analysis I
SELECT TOP 1 WITH TIES seller_id
FROM Sales
GROUP BY seller_id
ORDER BY SUM(price) DESC;
1141. 查詢近30天活躍用戶數
SELECT activity_date as day, COUNT(DISTINCT user_id) as active_users
FROM Activity
WHERE activity_date between '2019-06-28' and '2019-07-27'
GROUP BY activity_date;
1148. 文章瀏覽 I
SELECT DISTINCT author_id as id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;
1149. Article Views II
SELECT DISTINCT viewer_id as id
FROM Views
GROUP BY viewer_id, view_date
HAVING COUNT(DISTINCT article_id) > 1
ORDER BY id;
182. 查找重復的電子郵箱
聚合函數（如 COUNT）通常需要與 GROUP BY 子句一起使用，并且過濾條件應該放在 HAVING 子句中。直接在 WHERE 子句中使用聚合函數會導致語法錯誤
SELECT email as email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;
511. 游戲玩法分析 I
處理聚合查詢時，MIN 是一個更通用的解決方案，適用于所有 SQL 數據庫。TOP 1 則更適合用于非聚合查詢中選擇排序后的第一行記錄
SELECT player_id, MIN(event_date) as first_login
FROM Activity
GROUP BY player_id;
578. Get Highest Answer Rate Question
SELECT TOP 1 question_id as survey_log
FROM survey_log
GROUP BY question_id
ORDER BY COUNT(answer_id) * 1.0 / (COUNT(*) - COUNT(answer_id)) DESC;
584. 尋找用戶推薦人
SELECT name
FROM Customer
WHERE referee_id != 2 OR referee_id IS NULL;
586. 訂單最多的客戶
SELECT customer_number
FROM orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;
595. 大的國家
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;
596. 超過5名學生的課
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5;
619. 只出現一次的最大數字
多一層為了空表格時輸出null
SELECT (SELECT numFROM MyNumbersGROUP BY numHAVING COUNT(*) = 1ORDER BY num DESCLIMIT 1
) as num;
620. 有趣的電影
SELECT *
FROM cinema
WHERE description != 'boring' AND id % 2 = 1
ORDER BY rating DESC;
