---
# System prepended metadata

title: ' SQL 50 NORMAL | SELECT, BASIC JOINS, Basic Aggregate Functions, Sorting and Grouping'

---

# 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

![截圖 2025-11-20 00.46.28](https://hackmd.io/_uploads/SJZKVujxbe.png)

![截圖 2025-11-20 00.46.32](https://hackmd.io/_uploads/Bkbt4diebx.png)

```sql=
SELECT product_id
FROM Products
WHERE low_fats="Y" and recyclable="Y";
```

<br/>

### EASY 584. Find Customer Referee

![截圖 2025-11-20 00.46.03](https://hackmd.io/_uploads/SJNqV_olZe.png)

![截圖 2025-11-20 00.46.08](https://hackmd.io/_uploads/H1V9EuogZl.png)


```=sql
SELECT name
FROM Customer
WHERE referee_id <> 2 OR referee_id IS NULL;
```

<br/>

### EASY 595. Big Countries

![截圖 2025-11-20 00.48.06](https://hackmd.io/_uploads/rJ7YHuslbx.png)

![截圖 2025-11-20 00.50.53](https://hackmd.io/_uploads/S1mFr_sxWx.png)

```=sql
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;
```

<br/>

### EASY 1148. Article Views I

![截圖 2025-11-20 00.56.25](https://hackmd.io/_uploads/HJD0IdjeZl.png)

![截圖 2025-11-20 00.56.31](https://hackmd.io/_uploads/r1D0Ldog-x.png)

```=sql
SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;
```

<br/>

### Easy 1683. Invalid Tweets

![截圖 2025-11-20 01.01.49](https://hackmd.io/_uploads/SJJ-tdsx-l.png)

![截圖 2025-11-20 01.01.53](https://hackmd.io/_uploads/B1yWFdig-l.png)


```=sql
SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;
```

<br/>

## Basic Join

### EASY 1378. Replace Employee ID With The Unique Identifier

![截圖 2025-11-20 01.12.45](https://hackmd.io/_uploads/S1hn9dse-x.png)

![截圖 2025-11-20 01.12.51](https://hackmd.io/_uploads/Hy2n5Oilbl.png)

```=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

![截圖 2025-11-20 01.17.16](https://hackmd.io/_uploads/SJPlndol-x.png)

![截圖 2025-11-20 01.17.21](https://hackmd.io/_uploads/r1wg2OjeZl.png)

```=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

![截圖 2025-11-20 01.22.18](https://hackmd.io/_uploads/r1pjAdjlbl.png)

![截圖 2025-11-20 01.22.38](https://hackmd.io/_uploads/rkaiCOilWx.png)


```=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

![截圖 2025-11-20 01.31.16](https://hackmd.io/_uploads/SJyD-Kogbg.png)

![截圖 2025-11-21 15.40.48](https://hackmd.io/_uploads/By-sv5px-x.png)


```=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

![截圖 2025-11-20 20.48.09](https://hackmd.io/_uploads/ByvjCtneWx.png)

![截圖 2025-11-20 20.48.27](https://hackmd.io/_uploads/rJDoAK3lZx.png)

```=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

![截圖 2025-11-20 20.51.34](https://hackmd.io/_uploads/SyDW152xbl.png)

![截圖 2025-11-21 15.48.53](https://hackmd.io/_uploads/SkC_t5peZg.png)

```=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

![截圖 2025-11-20 21.52.01](https://hackmd.io/_uploads/B1dGT9hlWe.png)


![截圖 2025-11-20 21.52.05](https://hackmd.io/_uploads/BkOGp53ebe.png)


```=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

![截圖 2025-11-20 21.57.29](https://hackmd.io/_uploads/rJp9Cq2lWg.png)

![截圖 2025-11-20 21.58.04](https://hackmd.io/_uploads/Skp5A93gZl.png)

```=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

![截圖 2025-11-20 22.22.59](https://hackmd.io/_uploads/SJRLVsne-e.png)

![截圖 2025-11-20 22.23.03](https://hackmd.io/_uploads/rkCUEs3gbg.png)


```=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

![截圖 2025-11-20 22.33.31](https://hackmd.io/_uploads/rkwCIo2xZg.png)

![截圖 2025-11-20 22.33.35](https://hackmd.io/_uploads/SJwRIo3e-e.png)


```=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

![截圖 2025-11-20 23.17.53](https://hackmd.io/_uploads/B1O5Z23eZx.png)


![截圖 2025-11-20 23.17.49](https://hackmd.io/_uploads/rkO9Z33eZg.png)


```=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

![截圖 2025-11-20 23.33.57](https://hackmd.io/_uploads/rkEWS2hg-e.png)

![截圖 2025-11-20 23.34.05](https://hackmd.io/_uploads/rJNbSn2l-e.png)

原本寫這樣

```=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;
```

![截圖 2025-11-20 23.35.11](https://hackmd.io/_uploads/BkOir3ngbl.png)


其實可以直接算 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

![截圖 2025-11-21 16.48.02](https://hackmd.io/_uploads/rJDtPjpl-g.png)

![截圖 2025-11-21 16.47.57](https://hackmd.io/_uploads/r1wtws6gWe.png)


```=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

![截圖 2025-11-21 17.01.52](https://hackmd.io/_uploads/HJgRqsagbg.png)


![截圖 2025-11-21 17.01.56](https://hackmd.io/_uploads/BJgC9s6xbx.png)


```=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

![截圖 2025-11-21 17.14.32](https://hackmd.io/_uploads/HyhMk36g-g.png)

![截圖 2025-11-21 17.55.50](https://hackmd.io/_uploads/rJCEDhplZg.png)


原本我只寫這樣，之後細看要找 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;
```
![截圖 2025-11-21 17.22.44](https://hackmd.io/_uploads/SySd1npgbg.png)

```=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

![截圖 2025-11-21 17.48.11](https://hackmd.io/_uploads/HyB6ShpeZl.png)

![截圖 2025-11-21 17.48.15](https://hackmd.io/_uploads/r14prnplZe.png)

```=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

![截圖 2025-11-21 18.26.10](https://hackmd.io/_uploads/r1zPRhTxZx.png)

![截圖 2025-11-21 18.26.17](https://hackmd.io/_uploads/SJGvRhae-g.png)


```=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

![截圖 2025-11-21 18.35.28](https://hackmd.io/_uploads/ByNtgppx-l.png)

![截圖 2025-11-21 18.35.31](https://hackmd.io/_uploads/SyVKxaTe-e.png)

```=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

![截圖 2025-11-21 18.41.21](https://hackmd.io/_uploads/SyTWfppxWe.png)

![截圖 2025-11-21 18.41.25](https://hackmd.io/_uploads/B16ZfpTeWl.png)


```=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


![截圖 2025-11-21 18.51.11](https://hackmd.io/_uploads/Bk3BE6pxZe.png)

![截圖 2025-11-21 18.51.14](https://hackmd.io/_uploads/Sk3r4pTgbg.png)


```=sql
SELECT 
    class
FROM Courses
GROUP BY(class)
HAVING COUNT(*) >= 5;
```

<br/>

### EASY 1729. Find Followers Count

![截圖 2025-11-21 18.57.52](https://hackmd.io/_uploads/BkVaBpax-e.png)

![截圖 2025-11-21 18.57.49](https://hackmd.io/_uploads/H14THTTgZe.png)


```=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

![截圖 2025-11-21 19.06.19](https://hackmd.io/_uploads/Sk50vpalZl.png)

![截圖 2025-11-21 19.06.23](https://hackmd.io/_uploads/HycRvaplWl.png)


```=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

![截圖 2025-11-21 19.30.49](https://hackmd.io/_uploads/rk_tTppxZx.png)


![截圖 2025-11-21 19.30.54](https://hackmd.io/_uploads/ByuF6p6e-x.png)


```=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;
```