# SQL 50 NORMAL | SELECT, BASIC JOINS, Basic Aggregate Functions, Sorting and Grouping
```=
- SELETE (解題技巧)
- EASY 1757. Recyclable and Low Fat Products (WHERE...AND/OR...)
- EASY 584. Find Customer Referee (WHERE =/<>/IS NULL/IS NOT NULL)
- EASY 595. Big Countries (WHETE >=/<=)
- EASY 1148. Article Views I (SELECT DISTINCT, ORDER BY)
- Easy 1683. Invalid Tweets (LENGTH(columns_name))
- Basic Join (解題技巧)
- EASY 1378. Replace Employee ID With The Unique Identifier (LEFT JOIN...ON...)
- EASY 1068. Product Sales Analysis I (GROUP BY...COUNT(*) AS...)
- EASY 197. Rising Temperature (DATE_SUB(table.column_name, INTERVAL 1 DAY))
- EASY 577. Employee Bonus
- EASY 1280. Students and Examinations (CROSS JOIN 兩者相乘的大數據集)
- MEDIUM 570. Managers with at Least 5 Direct Reports (HAVING COUNT(*))
- MEDIUM 1934. Confirmation Rate (ROUND(IFNULL(分子/分母, 0), 2))
- Basic Aggregrate Functions (解題技巧)
- EASY 620. Not Boring Movies (ORDER BY rating DESC/ASC)
- EASY 1251. Average Selling Price (u.purchase_date BETWEEN p.start_date AND p.end_date)
- EASY 1075. Project Employees I
- EASY 1633. Percentage of Users Attended a Contest (ROUND(COUNT(DISTINCT table.column_name) * 100.0 / (SELECT COUNT(*) FROM table),2))
- EASY 1211. Queries Quality and Percentage (AVG(CASE WHEN rating < 3 THEN 1 ELSE 0 END))
- MEDIUM 1193. Monthly Transactions I (DATE_FORMAT(trans_date, '%Y-%m'), CASE WHEN...THEN...ELSE...END)
- MEDIUM 1174. Immediate Food Delivery II (INNER JOIN)
- MEDIUM 550. Game Play Analysis IV (CTE：WITH cte_name AS(SELECT...))
- Sorting and Grouping (解題技巧)
- EASY 2356. Number of Unique Subjects Taught by Each Teacher (COUNT(DISTINCT column_name)
- EASY 1141. User Activity for the Past 30 Days I
- MEDIUM 1070. Product Sales Analysis III (WITH, INNER JOIN)
- EASY 596. Classes With at Least 5 Students (HAVING COUNT(*))
- EASY 1729. Find Followers Count
- EASY 619. Biggest Single Number (MAX(num))
- EASY 1045. Customers Who Bought All Products
- GPT 出題練習 (解題技巧)
-
```
## SELETE
### EASY 1757. Recyclable and Low Fat Products
```sql=
SELECT product_id
FROM Products
WHERE low_fats="Y" and recyclable="Y";
```
<br/>
### EASY 584. Find Customer Referee
```=sql
SELECT name
FROM Customer
WHERE referee_id <> 2 OR referee_id IS NULL;
```
<br/>
### EASY 595. Big Countries
```=sql
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;
```
<br/>
### EASY 1148. Article Views I
```=sql
SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;
```
<br/>
### Easy 1683. Invalid Tweets
```=sql
SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;
```
<br/>
## Basic Join
### EASY 1378. Replace Employee ID With The Unique Identifier
```=sql
SELECT eu.unique_id, e.name
FROM Employees e
LEFT JOIN EmployeeUNI eu
ON e.id=eu.id;
```
<br/>
## EASY 1068. Product Sales Analysis I
```=sql
SELECT p.product_name, s.year, s.price
FROM Sales s
LEFT JOIN Product p
ON s.product_id = p.product_id;
```
<br/>
## EASY 1581. Customer Who Visited but Did Not Make Any Transactions
```=sql
SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t
ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id;
```
<br/>
### EASY 197. Rising Temperature
```=sql
SELECT W1.id
FROM Weather W1
LEFT JOIN Weather W2
ON W2.recordDate = DATE_SUB(W1.recordDate, INTERVAL 1 DAY)
WHERE W1.temperature > W2.temperature;
```
<br/>
### EASY 577. Employee Bonus
```=sql
SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b
ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;
```
<br/>
### EASY 1280. Students and Examinations
```=sql
SELECT
s.student_id,
s.student_name,
sub.subject_name,
COUNT(e.subject_name) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e
ON s.student_id = e.student_id
AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;
```
<br/>
### MEDIUM 570. Managers with at Least 5 Direct Reports
```=sql
SELECT e1.name
FROM Employee e1
LEFT JOIN Employee e2
ON e1.id = e2.managerId
GROUP BY e1.id, e1.name
HAVING COUNT(*) >= 5;
```
<br/>
### MEDIUM 1934. Confirmation Rate
```=sql
SELECT s.user_id,
ROUND(IFNULL(SUM(c.action = 'confirmed') / COUNT(c.action), 0), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c
ON s.user_id = c.user_id
GROUP BY s.user_id;
```
<br/>
## Basic Aggregrate Functions
### EASY 620. Not Boring Movies
```=sql
SELECT id, movie, description, rating
FROM Cinema
WHERE id%2=1 AND description <> "boring"
ORDER BY rating DESC;
```
<br/>
### EASY 1251. Average Selling Price
```=sql
SELECT p.product_id, ROUND(IFNULL(SUM(p.price * u.units) / SUM(u.units),0),2) AS average_price
FROM Prices p
LEFT JOIN UnitsSold u
ON p.product_id = u.product_id
AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;
```
<br/>
### EASY 1075. Project Employees I
```=sql
SELECT p.project_id, ROUND(IFNULL(AVG(e.experience_years),0),2) AS average_years
FROM Project p
LEFT JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY p.project_id;
```
<br/>
## EASY 1633. Percentage of Users Attended a Contest
原本寫這樣
```=sql
SELECT r.contest_id, ROUND(COUNT(DISTINCT r.user_id) * 100.0 / (SELECT COUNT(*) FROM Users),2) AS percentage
FROM Users u
LEFT JOIN Register r
ON u.user_id = r.user_id
GROUP BY r.contest_id
ORDER BY percentage DESC , r.contest_id ASC;
```
其實可以直接算 User 人數
```=sql
SELECT
r.contest_id,
ROUND(
COUNT(DISTINCT r.user_id) * 100.0 / (SELECT COUNT(*) FROM Users),
2
) AS percentage
FROM Register r
GROUP BY r.contest_id
ORDER BY percentage DESC, r.contest_id ASC;
```
<br/>
### EASY 1211. Queries Quality and Percentage
```=sql
SELECT
query_name,
ROUND(AVG(rating / position),2) AS quality,
ROUND(AVG(CASE WHEN rating < 3 THEN 1 ELSE 0 END) * 100, 2) AS poor_query_percentage
FROM Queries
GROUP BY query_name;
```
<br/>
### MEDIUM 1193. Monthly Transactions I
```=sql
SELECT
DATE_FORMAT(trans_date, '%Y-%m') AS month,
country,
COUNT(*) AS trans_count,
SUM(state="approved") AS approved_count,
SUM(amount) AS trans_total_amount,
SUM(CASE WHEN state="approved" THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY month, country;
```
<br/>
### 1174. Immediate Food Delivery II
原本我只寫這樣，之後細看要找 first order
```=sql
SELECT
ROUND(AVG(CASE WHEN customer_pref_delivery_date = order_date THEN 1 ELSE 0 END) * 100, 2) AS immediate_percentage
FROM Delivery;
```
```=sql
SELECT
ROUND(AVG(CASE WHEN d.customer_pref_delivery_date = d.order_date THEN 1 ELSE 0 END) * 100, 2) AS immediate_percentage
FROM Delivery d
INNER JOIN (
SELECT customer_id, MIN(order_date) AS first_order_date
FROM Delivery
GROUP BY customer_id
) d_first
ON d.customer_id = d_first.customer_id
AND d.order_date = d_first.first_order_date;
```
<br/>
### MEDIUM 550. Game Play Analysis IV
```=sql
WITH
first_logins AS (
SELECT
a.player_id,
MIN(a.event_date) AS first_login
FROM Activity a
GROUP BY a.player_id
),
consec_logins AS (
SELECT
COUNT(a.player_id) AS num_logins
FROM first_logins f
INNER JOIN Activity a
ON f.player_id = a.player_id
AND f.first_login = DATE_SUB(a.event_date, INTERVAL 1 DAY)
)
SELECT
ROUND(
(SELECT c.num_logins FROM consec_logins c) / (SELECT COUNT(f.player_id) FROM first_logins f)
, 2) AS fraction;
```
<br/>
## Sorting and Grouping
### EASY 2356. Number of Unique Subjects Taught by Each Teacher
```=sql
SELECT
teacher_id,
COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id;
```
<br/>
### EASY 1141. User Activity for the Past 30 Days I
```=sql
SELECT
activity_date AS day,
COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY) AND '2019-07-27'
GROUP BY activity_date;
```
<br/>
## MEDIUM 1070. Product Sales Analysis III
```=sql
WITH
first_year_table AS (
SELECT
product_id,
MIN(year) AS first_year
FROM Sales
GROUP BY product_id
)
SELECT
s.product_id,
f.first_year,
s.quantity,
s.price
FROM Sales s
INNER JOIN first_year_table f
ON s.product_id = f.product_id
AND s.year = f.first_year;
```
<br/>
### EASY 596. Classes With at Least 5 Students
```=sql
SELECT
class
FROM Courses
GROUP BY(class)
HAVING COUNT(*) >= 5;
```
<br/>
### EASY 1729. Find Followers Count
```=sql
SELECT
user_id,
COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id ASC;
```
<br/>
## EASY 619. Biggest Single Number
```=sql
WITH single_num AS(
SELECT num
FROM MyNumbers
GROUP BY num
Having COUNT(*) = 1
)
SELECT
MAX(num) AS num
FROM single_num;
```
<br/>
## EASY 1045. Customers Who Bought All Products
```=sql
SELECT customer_id
FROM Customer
WHERE product_key IN (SELECT product_key FROM Product)
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);
```
<br/>
## GPT 出題練習
### 找出每個員工在公司裡薪水的排名（salary 越高，排名越前），同薪水給相同排名（dense_rank）
| 函數 | 用途 | 相同分數 | 名次例子 |
| ---------------- | ------- | ------------ | ------- |
| **ROW_NUMBER()** | 排序後逐行編號 | ❌ 不同名次 | 1,2,3,4 |
| **RANK()** | 競賽排名 | ✔ 相同名次 + 會跳號 | 1,1,3,4 |
| **DENSE_RANK()** | 無缺口排名 | ✔ 相同名次 + 不跳號 | 1,1,2,3 |
Employees(employee_id, name, salary)
```=sql
SELECT
employee_id,
name,
salary,
DENSE_RANK() OVER(ORDER BY salary DESC) AS salary_rank
FROM Employees;
```
<br/>
### 對每個部門找出薪水最高的那位員工
Employees(emp_id, dept_id, salary)
```=sql
WITH top_salary AS (
SELECT
dept_id,
MAX(salary) AS max_salary
FROM Employees
GROUP BY dept_id
)
SELECT
e.emp_id,
e.dept_id,
e.salary
FROM Employees e
LEFT JOIN top_salary t
ON e.dept_id = t.dept_id
AND e.salary = t.max_salary;
```
<br/>
### 找出每位顧客第一次交易後的下一次交易日期（LEAD）
LEAD()：取得下一列的值（往後看）
ROW_NUMBER()：同分區下，依排序給連續編號（1,2,3,4,...）
Transactions(customer_id, tran_date)
找出每位 customer 的
第一筆交易日期
下一筆交易日期（如沒有為 NULL）
```=sql
SELECT *
FROM (
SELECT
customer_id,
tran_date AS first_tran,
LEAD(tran_date) OVER (
PARTITION BY customer_id
ORDER BY tran_date
) AS next_tran,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY tran_date
) AS rn
FROM Transactions
) t
WHERE rn = 1;
```
<br/>
### 找出銷量前 3 名的產品（Window Function + Aggregation）
Sales(product_id, quantity)
找出總銷量最高的前 3 個產品
```=sql
WITH
total_sales AS (
SELECT
product_id,
SUM(quantity) AS total_qty
FROM Sales
GROUP BY product_id
)
SELECT
product_id,
total_qty
FROM (
SELECT
product_id,
total_qty,
DENSE_RANK() OVER(ORDER BY total_qty DESC) AS rnk
FROM total_sales
) t
WHERE rnk <= 3;
```
<br/>
### 找出從未購買任何商品的使用者（NOT EXISTS）
Users(user_id)
Orders(user_id, order_id)
找出從來沒有下過訂單的 user
```=sql
SELECT u.user_id
FROM Users u
WHERE NOT EXISTS (
SELECT 1
FROM Orders o
WHERE o.user_id = u.user_id
);
```
<br/>
### 找出每位顧客連續消費的天數（Window + LAG）
Orders(customer_id, order_date)
一個 user 只要連續兩天都有消費就視為同一段連續區間
求出每位客戶的 最長連續消費天數
LAG()：把「上一筆 order_date」搬到同一列
```=sql
WITH
ordered AS (
SELECT
customer_id,
order_date,
LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS prev_date
FROM Orders
),
grp AS (
SELECT
customer_id,
order_date,
CASE
WHEN DATEDIFF(order_date, prev_date) = 1
THEN 0
ELSE 1
END AS is_new_group
FROM ordered
),
grp_id AS (
SELECT
customer_id,
order_date,
SUM(is_new_group) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS group_id
FROM grp
)
SELECT
customer_id,
MAX(COUNT(*)) AS longest_streak
FROM grp_id
GROUP BY customer_id, group_id;
```
<br/>
### ETF vs Stock 交易占比
```=sql
SELECT
SUM(CASE WHEN product_type = 'ETF' THEN amount ELSE 0 END) AS etf_amt,
SUM(CASE WHEN product_type = 'STOCK' THEN amount ELSE 0 END) AS stock_amt
FROM trades;
```
### 查出超過 10 天未交易的客戶
```=sql
SELECT customer_id
FROM trades
GROUP BY customer_id
HAVING MAX(trade_time) < DATE_SUB(CURDATE(), INTERVAL 10 DAY);
```
### 每個客戶的累積交易金額（Window SUM）
```=sql
SELECT
customer_id,
trade_time,
amount,
SUM(amount) OVER (
PARTITION BY customer_id ORDER BY trade_time
) AS cum_amount
FROM trades;
```
Sign in with Wallet
Connect another wallet