1
FROM + JOIN     → First determine the data source and joins
2
WHERE           → Row-level filtering
3
GROUP BY        → Grouping
4
HAVING          → Group-level filtering
5
SELECT          → Select columns / aggregate / window functions
6
ORDER BY        → Sorting
7
LIMIT           → Truncation

1
SELECT name, population, area
2
FROM World
3
WHERE area >= 3000000
4
   OR population >= 25000000;

1
SELECT name
2
FROM Customer
3
WHERE referee_id != 2 OR referee_id IS NULL;

1
SELECT tweet_id
2
FROM Tweets
3
WHERE LENGTH(content) > 15;

1
SELECT (SELECT DISTINCT salary
2
        FROM Employee
3
        ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS SecondHighestSalary;

1
Original Employee table    → DISTINCT deduplicate    → ORDER BY DESC    → LIMIT 1 OFFSET 1
2
┌────┬────────┐           ┌────────┐                ┌────────┐        ┌────────┐
3
│ id │ salary │           │ salary │                │ salary │        │ salary │
4
├────┼────────┤           ├────────┤                ├────────┤        ├────────┤
5
│ 1  │ 100    │           │ 100    │                │ 400    │        │ 200    │
6
│ 2  │ 200    │   →       │ 200    │     →          │ 300    │   →    │        │
7
│ 3  │ 300    │           │ 300    │                │ 200    │        │        │
8
│ 4  │ 300    │           │ 400    │                │ 100    │        │        │
9
│ 5  │ 400    │           └────────┘                └────────┘        └────────┘
10
└────┴────────┘

1
SELECT *
2
FROM cinema
3
WHERE description != 'boring' AND id % 2 = 1
4
ORDER BY rating DESC;

1
 INNER (inner join)            LEFT (left join)
2
┌────┐  ┌────┐            ┌────┐  ┌────┐
3
│    │  │    │            │████│  │    │
4
│  ██│  │██  │            │████│  │██  │
5
│  ██│  │██  │            │████│  │██  │
6
│    │  │    │            │████│  │    │
7
└────┘  └────┘            └────┘  └────┘
8
 left right    left right        left right    left right
9

10
Using the same set of data to see the result differences (A = left table Person, B = right table Address, join key id):
11

12
A table (Person)              B table (Address)
13
┌────┬──────┐              ┌────┬──────┐
14
│ id │ name │              │ id │ city │
15
├────┼──────┤              ├────┼──────┤
16
│ 1  │ Allen│              │ 2  │ NYC  │
17
│ 2  │ Bob  │              │ 3  │ Boston│
18
│ 3  │ Zack │              └────┴──────┘
19
└────┴──────┘
20

21
① INNER JOIN (inner join) — keeps only rows where A and B can match by id
22
┌────┬──────┬──────┐
23
│ id │ name │ city │
24
├────┼──────┼──────┤
25
│ 2  │ Bob  │ NYC  │   ← A.2 = B.2 ✅
26
│ 3  │ Zack │ Boston│  ← A.3 = B.3 ✅
27
└────┴──────┴──────┘
28
(Allen has no match in B → does not appear)
29

30
② LEFT JOIN (left join) — keeps all rows of A; B fills with NULL when there is no match
31
┌────┬──────┬──────┐
32
│ id │ name │ city │
33
├────┼──────┼──────┤
34
│ 1  │ Allen│ NULL │   ← B has no match → city is NULL
35
│ 2  │ Bob  │ NYC  │
36
│ 3  │ Zack │ Boston│
37
└────┴──────┴──────┘
38

39
③ SELF JOIN — use the same table as two tables, distinguished by aliases
40
   In Employee: employee.managerId = manager.id (see 0181 / 1731 / 0570 for details)

1
SELECT p.firstName, p.lastName, a.city, a.state
2
FROM Person p
3
         LEFT JOIN Address a ON p.personId = a.personId;

1
SELECT e1.name AS Employee
2
FROM Employee e1
3
         JOIN Employee e2 ON e1.managerId = e2.id
4
WHERE e1.salary > e2.salary;

1
SELECT name, bonus
2
FROM Employee e
3
         LEFT JOIN Bonus b ON e.empId = b.empId
4
WHERE b.bonus < 1000
5
   OR b.bonus IS NULL;

1
SELECT euni.unique_id, e.name
2
FROM Employees e
3
         LEFT JOIN EmployeeUNI euni ON e.id = euni.id;

1
SELECT p.product_name, s.year, s.price
2
FROM Sales s
3
         JOIN Product p ON s.product_id = p.product_id;

1
SELECT v.customer_id, COUNT(v.customer_id) AS count_no_trans
2
FROM Visits v
3
         LEFT JOIN Transactions t ON v.visit_id = t.visit_id
4
WHERE t.transaction_id IS NULL
5
GROUP BY v.customer_id;

1
SELECT s.student_id,
2
       s.student_name,
3
       su.subject_name,
4
       COUNT(e.subject_name) AS attended_exams
5
FROM Students s
6
         JOIN Subjects su -- first do the cartesian product
7
         LEFT JOIN Examinations e -- then left join the examinations table
8
                   ON e.student_id = s.student_id AND e.subject_name = su.subject_name
9
GROUP BY s.student_id, su.subject_name
10
ORDER BY s.student_id, su.subject_name;

1
Students × Subjects (cartesian product):
2
┌─────────────┬──────────┐
3
│ student_id  │ subject  │
4
├─────────────┼──────────┤
5
│ 1, Alice    │ Math     │  ← LEFT JOIN Exam: found 1 row → count=1
6
│ 1, Alice    │ Physics  │  ← LEFT JOIN Exam: found 0 rows → count=0
7
│ 1, Alice    │ Math     │  ...
8
│ 2, Bob      │ Math     │
9
│ 2, Bob      │ Physics  │
10
└─────────────┴──────────┘
11

12
→ LEFT JOIN Examinations (keep all student×subject combinations)
13
→ GROUP BY (student_id, subject_name)
14
→ COUNT(e.subject_name) only counts non-NULL

1
SELECT e2.employee_id,
2
       e2.name,
3
       COUNT(e1.reports_to)  AS reports_count,
4
       ROUND(AVG(e1.age), 0) AS average_age
5
FROM Employees e1
6
         JOIN Employees e2 ON e1.reports_to = e2.employee_id
7
GROUP BY e1.reports_to
8
ORDER BY e2.employee_id;

1
SELECT e1.employee_id
2
FROM Employees e1
3
         LEFT JOIN Employees e2 ON e1.manager_id = e2.employee_id
4
WHERE e1.salary < 30000
5
  AND e2.employee_id IS NULL
6
  AND e1.manager_id IS NOT NULL
7
ORDER BY e1.employee_id;

1
WHERE: filter rows before grouping        HAVING: filter groups after grouping
2
┌─────────────┐              ┌─────────────┐
3
│  Raw data   │              │ Grouped result │
4
│  WHERE filter│ → GROUP BY → │ HAVING filter  │ → Final result
5
│  (row-level)│              │ (group-level)  │
6
└─────────────┘              └─────────────┘
7
Cannot use aggregate functions          Can use aggregate functions

1
SELECT player_id, MIN(event_date) AS first_login
2
FROM Activity
3
GROUP BY player_id;

1
SELECT email AS Email
2
FROM Person
3
GROUP BY email
4
HAVING COUNT(email) > 1;

1
SELECT class
2
FROM Courses
3
GROUP BY class
4
HAVING COUNT(DISTINCT student) >= 5;

1
SELECT e1.name
2
FROM Employee e1
3
         JOIN Employee e2 ON e1.Id = e2.managerId
4
GROUP BY e1.Id
5
HAVING COUNT(e2.Id) >= 5;

1
Employee table (self join)
2
e1=manager, e2=subordinate
3
┌─────────────┬──────────────────┐
4
│ e1.name(manager) │ e2.Id(subordinate) │
5
├─────────────┼──────────────────┤
6
│ Stephen     │ 1, 2, 3, 4, 5    │  → COUNT=5 ✅
7
│ Alice       │ 6, 7             │  → COUNT=2 ❌
8
└─────────────┴──────────────────┘
9
→ GROUP BY e1.Id → HAVING COUNT >= 5 → Stephen

1
SELECT customer_id
2
FROM Customer
3
GROUP BY customer_id
4
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

1
Product table has 3 products in total (product_key: 1, 2, 3)
2
→ subquery: SELECT COUNT(*) FROM Product = 3
3

4
Customer table:
5
┌─────────────┬────────────┐
6
│ customer_id │ product_key│
7
├─────────────┼────────────┤
8
│ 1           │ 1          │
9
│ 1           │ 2          │
10
│ 1           │ 3          │   → COUNT(DISTINCT)=3 = 3 ✅
11
│ 2           │ 1          │
12
│ 2           │ 2          │   → COUNT(DISTINCT)=2 ≠ 3 ❌
13
│ 3           │ 1          │
14
│ 3           │ 2          │
15
│ 3           │ 3          │
16
│ 3           │ 1(duplicate)│   → COUNT(DISTINCT)=3 = 3 ✅
17
└─────────────┴────────────┘
18

19
Final result: customer_id = 1, 3

1
SELECT p.product_id, p.product_name
2
FROM Product p
3
         JOIN Sales s ON p.product_id = s.product_id
4
GROUP BY s.product_id
5
HAVING MIN(s.sale_date) >= '2019-01-01'
6
   AND MAX(s.sale_date) <= '2019-03-31';

1
SELECT u.name, SUM(t.amount) AS balance
2
FROM Users u
3
         JOIN Transactions t ON u.account = t.account
4
GROUP BY t.account
5
HAVING SUM(t.amount) > 10000;

1
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country,
2
       COUNT(state) AS trans_count,
3
       SUM(IF(state = 'approved', 1, 0)) AS approved_count,
4
       SUM(amount) AS trans_total_amount,
5
       SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
6
FROM Transactions
7
GROUP BY month, country;

1
SELECT stock_name,
2
       SUM(CASE
3
               WHEN operation = 'Buy' THEN -price
4
               WHEN operation = 'Sell' THEN price END) AS capital_gain_loss
5
FROM Stocks
6
GROUP BY stock_name;

1
SELECT sell_date,
2
       COUNT(DISTINCT product)                         AS num_sold,
3
       GROUP_CONCAT(DISTINCT product ORDER BY product) AS products
4
FROM Activities
5
GROUP BY sell_date;

1
SELECT date_id,
2
       make_name,
3
       COUNT(DISTINCT lead_id)    AS unique_leads,
4
       COUNT(DISTINCT partner_id) AS unique_partners
5
FROM DailySales
6
GROUP BY date_id, make_name;

1
SELECT MAX(num) AS num
2
FROM (SELECT num
3
      FROM MyNumbers
4
      GROUP BY num
5
      HAVING COUNT(num) = 1) AS numbers;

1
Original MyNumbers:          → GROUP BY num + HAVING COUNT=1:    → MAX():
2
┌──────┐                ┌──────┐                           ┌──────┐
3
│ num  │                │ num  │                           │ num  │
4
├──────┤                ├──────┤                           ├──────┤
5
│ 8    │                │ 8    │ ← appears 1 time ✅       │      │
6
│ 8    │                │ 6    │ ← appears 1 time ✅       │  6   │
7
│ 3    │                │      │  3 appears 2 times ❌     │      │
8
│ 3    │                │      │  1 appears 3 times ❌     │      │
9
│ 7    │                │ 7    │ ← appears 1 time ✅       │      │
10
│ 6    │                └──────┘                           └──────┘
11
│ 6    │
12
│ 1    │
13
│ 1    │
14
│ 1    │
15
└──────┘

1
SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
2
FROM Insurance
3
WHERE (lat, lon) IN
4
      (SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(pid) = 1)
5
  AND tiv_2015 IN
6
      (SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(pid) > 1);

1
Original Insurance table:
2
┌─────┬──────────┬──────────┬──────┬──────┐
3
│ pid │ tiv_2015 │ tiv_2016 │ lat  │ lon  │
4
├─────┼──────────┼──────────┼──────┼──────┤
5
│ 1   │ 10       │ 5        │ 10   │ 10   │
6
│ 2   │ 20       │ 20       │ 20   │ 20   │
7
│ 3   │ 10       │ 30       │ 20   │ 20   │  ← lat,lon same as pid=2!
8
│ 4   │ 10       │ 40       │ 40   │ 40   │
9
└─────┴──────────┴──────────┴──────┴──────┘
10

11
Subquery 1: unique city coordinates → (10,10) and (40,40) → pid=1, 4
12
Subquery 2: duplicated 2015 insured amounts → tiv_2015=10 (appears 3 times) → pid=1,3,4
13

14
Intersection of the two conditions: pid=1 and pid=4
15
→ SUM(tiv_2016) = 5 + 40 = 45

1
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
2
FROM Employee e
3
         JOIN Department d ON e.departmentId = d.id
4
WHERE (SELECT COUNT(DISTINCT e2.salary)
5
       FROM Employee e2
6
       WHERE e.salary < e2.salary
7
         AND e.departmentId = e2.departmentId) < 3;

1
SELECT employee_id, department_id
2
FROM Employee
3
WHERE primary_flag = 'Y'
4
   OR employee_id IN (SELECT employee_id
5
                      FROM Employee
6
                      GROUP BY employee_id
7
                      HAVING COUNT(employee_id) = 1);

1
Employee table:
2
┌─────────────┬──────────────┬──────────────┐
3
│ employee_id │ department_id│ primary_flag │
4
├─────────────┼──────────────┼──────────────┤
5
│ 1           │ 1            │ N            │  ← only 1 department → subquery hits ✅
6
│ 2           │ 1            │ Y            │  ← primary_flag=Y ✅
7
│ 2           │ 2            │ N            │  ← satisfies no condition ❌
8
│ 3           │ 2            │ N            │  ← only 1 department → subquery hits ✅
9
│ 4           │ 1            │ N            │  ← has 2 departments, neither primary is Y → see:
10
│ 4           │ 2            │ N            │    subquery COUNT=2≠1 ❌, primary≠Y ❌
11
└─────────────┴──────────────┴──────────────┘
12

13
Final result: (1,1), (2,1), (3,2)

1
Regular GROUP BY: one row output per group     Window function: every row is output, but the aggregation scope is the "window"
2
┌────────────┐                                 ┌────────────────────────┐
3
│ dept │ AVG │                                 │ emp │ dept │ salary │ AVG(dept) │
4
├──────┼─────┤                                 ├─────┼──────┼────────┼──────────┤
5
│ A    │ 75  │                                 │ 1   │ A    │ 80     │ 75       │ ← average of same dept
6
│ B    │ 60  │                                 │ 2   │ A    │ 70     │ 75       │
7
└────────────┘                                 │ 3   │ B    │ 60     │ 60       │
8
                                              └─────┴──────┴────────┴──────────┘

1
SELECT person_name
2
FROM (SELECT person_name,
3
             turn,
4
             SUM(weight) OVER(ORDER BY turn) AS sum_weight
5
      FROM Queue) AS wei
6
WHERE sum_weight <= 1000
7
ORDER BY turn DESC LIMIT 1;

1
SELECT visited_on, amount, average_amount
2
FROM (SELECT visited_on,
3
             SUM(amount)  OVER(ORDER BY visited_on
4
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount, ROUND(AVG(amount) OVER(ORDER BY visited_on
5
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS average_amount,
6
             ROW_NUMBER() OVER(ORDER BY visited_on) AS rn
7
      FROM (SELECT visited_on, SUM(amount) AS amount
8
            FROM Customer
9
            GROUP BY visited_on) AS daily) AS ranked
10
WHERE rn > 6;

1
Step 1: Aggregate by day first
2
  visited_on  | amount (daily total)
3
  2019-01-01  | 130
4
  2019-01-02  | 110
5
  2019-01-03  | 140
6
  2019-01-04  | 100
7
  2019-01-05  | 110
8
  2019-01-06  | 130
9
  2019-01-07  | 150
10
  2019-01-08  | 120
11
  2019-01-09  | 200
12

13
Step 2: 7-day rolling window (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
14

15
  visited_on  | window range              | SUM   | AVG     | rn | >6?
16
  2019-01-01  | [01-01]                   | 130   | 130.00  | 1  | ❌
17
  2019-01-02  | [01-01 ~ 01-02]           | 240   | 120.00  | 2  | ❌
18
  ...         | window less than 7 days    | ...   | ...     | .. | ❌
19
  2019-01-07  | [01-01 ~ 01-07]           | 870   | 124.29  | 7  | ❌
20
  2019-01-08  | [01-02 ~ 01-08]           | 860   | 122.86  | 8  | ✅ → output
21
  2019-01-09  | [01-03 ~ 01-09]           | 950   | 135.71  | 9  | ✅ → output

1
WITH uion AS (SELECT u.user_id, u.name, m.title, mr.rating, mr.created_at
2
              FROM MovieRating mr
3
                       JOIN Users u ON mr.user_id = u.user_id
4
                       JOIN Movies m ON mr.movie_id = m.movie_id)
5
SELECT name AS results
6
FROM (SELECT name, RANK() OVER(ORDER BY COUNT(title) DESC, name) AS rk
7
      FROM uion
8
      GROUP BY user_id) AS max_user
9
WHERE rk = 1
10
UNION ALL
11
SELECT title AS results
12
FROM (SELECT title, RANK() OVER(ORDER BY AVG(rating) DESC, title) AS rk
13
      FROM uion
14
      WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
15
      GROUP BY title) AS max_title
16
WHERE rk = 1;

1
Step 1: CTE uion — three-table JOIN
2
┌────────┬────────┬─────────────┬───────┬────────────┐
3
│ user_id│ name   │ title       │ rating│ created_at │
4
├────────┼────────┼─────────────┼───────┼────────────┤
5
│ 3      │ Daniel │ Ice         │ 5     │ 2020-02-14  │
6
│ 2      │ Daniel │ Detention   │ 3     │ 2020-02-12  │
7
│ ...    │ ...    │ ...         │ ...   │ ...         │
8
└────────┴────────┴─────────────┴───────┴────────────┘
9

10
Step 2: Subquery 1 — rank users by number of comments
11
GROUP BY user_id → COUNT(title) → RANK() OVER(ORDER BY count DESC, name)
12
┌────────┬───────┬─────┐
13
│ name   │ count │ rk  │
14
├────────┼───────┼─────┤
15
│ Daniel │ 3     │ 1   │ ← WHERE rk=1 → output "Daniel"
16
│ Gloria │ 1     │ 2   │
17
└────────┴───────┴─────┘
18

19
Step 3: Subquery 2 — rank February movies by average rating
20
WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
21
GROUP BY title → AVG(rating) → RANK() OVER(ORDER BY avg DESC, title)
22
┌─────────────┬───────┬─────┐
23
│ title       │ avg   │ rk  │
24
├─────────────┼───────┼─────┤
25
│ Ice         │ 4.5   │ 1   │ ← WHERE rk=1 → output "Ice"
26
│ Detention   │ 3.0   │ 2   │
27
└─────────────┴───────┴─────┘
28

29
Step 4: UNION ALL merge
30
┌──────────┐
31
│ results  │
32
├──────────┤
33
│ Daniel   │
34
│ Ice      │
35
└──────────┘

1
SELECT x,
2
       y,
3
       z,
4
       CASE
5
           WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
6
           ELSE 'No' END AS triangle
7
FROM Triangle;

1
SELECT CASE
2
           WHEN id % 2 = 0 THEN id - 1
3
           WHEN id % 2 = 1 AND id != (SELECT COUNT(*) FROM Seat) THEN id + 1
4
       ELSE id
5
END
6
AS id, student
7
FROM Seat ORDER BY id;

1
UPDATE Salary
2
SET sex = CASE WHEN sex = 'm' THEN 'f' WHEN sex = 'f' THEN 'm' END;

1
SELECT 'Low Salary' AS category, SUM(IF(income < 20000, 1, 0)) AS accounts_count
2
FROM Accounts
3
UNION
4
SELECT 'Average Salary', SUM(IF(income >= 20000 AND income <= 50000, 1, 0))
5
FROM Accounts
6
UNION
7
SELECT 'High Salary', SUM(IF(income > 50000, 1, 0))
8
FROM Accounts;

1
Accounts table: income = [8000, 25000, 30000, 60000, 15000]
2

3
1st SELECT: 'Low Salary', SUM(IF(income<20000,1,0))
4
  → 8000→1, 25000→0, 30000→0, 60000→0, 15000→1 → SUM=2
5

6
2nd SELECT: 'Average Salary', SUM(IF(20000≤income≤50000,1,0))
7
  → 8000→0, 25000→1, 30000→1, 60000→0, 15000→0 → SUM=2
8

9
3rd SELECT: 'High Salary', SUM(IF(income>50000,1,0))
10
  → 8000→0, 25000→0, 30000→0, 60000→1, 15000→0 → SUM=1
11

12
UNION merge:
13
┌──────────────────┬───────────────┐
14
│ category         │ accounts_count│
15
├──────────────────┼───────────────┤
16
│ Low Salary       │ 2             │
17
│ Average Salary   │ 2             │
18
│ High Salary      │ 1             │
19
└──────────────────┴───────────────┘

1
UNION: merge with deduplication            UNION ALL: merge without deduplication
2
┌──────┐  ┌──────┐       ┌──────┐  ┌──────┐
3
│ A    │  │ A    │       │ A    │  │ A    │
4
│ B    │  │ C    │       │ B    │  │ C    │
5
└──────┘  └──────┘       └──────┘  └──────┘
6
    ↓                         ↓
7
┌──────┐                  ┌──────┐
8
│ A    │ ← deduplicated   │ A    │
9
│ B    │                  │ A    │ ← kept
10
│ C    │                  │ B    │
11
└──────┘                  │ C    │
12
                          └──────┘

1
SELECT product_id, 'store1' AS store, store1 AS price
2
FROM Products
3
WHERE store1 IS NOT NULL
4
UNION ALL
5
SELECT product_id, 'store2' AS store, store2 AS price
6
FROM Products
7
WHERE store2 IS NOT NULL
8
UNION ALL
9
SELECT product_id, 'store3' AS store, store3 AS price
10
FROM Products
11
WHERE store3 IS NOT NULL;

1
Original Products table (wide table):
2
┌────────────┬────────┬────────┬────────┐
3
│ product_id │ store1 │ store2 │ store3 │
4
├────────────┼────────┼────────┼────────┤
5
│ 0          │ 95     │ 100    │ 105    │
6
│ 1          │ 70     │ NULL   │ 80     │
7
└────────────┴────────┴────────┴────────┘
8

9
After UNION ALL (long table):
10
┌────────────┬────────┬───────┐
11
│ product_id │ store  │ price │
12
├────────────┼────────┼───────┤
13
│ 0          │ store1 │ 95    │
14
│ 0          │ store2 │ 100   │
15
│ 0          │ store3 │ 105   │
16
│ 1          │ store1 │ 70    │
17
│ 1          │ store3 │ 80    │  ← store2=NULL filtered out
18
└────────────┴────────┴───────┘

1
-- Products with change records: take the most recent price ≤ 2019-08-16
2
SELECT product_id, new_price AS price
3
FROM Products
4
WHERE (product_id, change_date) IN
5
      (SELECT product_id, MAX(change_date)
6
       FROM Products
7
       WHERE change_date <= '2019-08-16'
8
       GROUP BY product_id)
9
UNION
10
-- Products with no change records: price defaults to 10
11
SELECT product_id, 10 AS price
12
FROM Products
13
WHERE (product_id, change_date) IN
14
      (SELECT product_id, MIN(change_date)
15
       FROM Products
16
       GROUP BY product_id
17
       HAVING MIN(change_date) > '2019-08-16')
18
ORDER BY product_id;

1
Products table:
2
┌────────────┬───────────┬─────────────┐
3
│ product_id │ new_price │ change_date │
4
├────────────┼───────────┼─────────────┤
5
│ 1          │ 20        │ 2019-08-14  │  ← ≤ 08-16, most recent
6
│ 2          │ 50        │ 2019-08-01  │  ← ≤ 08-16, most recent
7
│ 1          │ 10        │ 2019-08-17  │  ← > 08-16
8
│ 3          │ 30        │ 2019-08-19  │  ← first change > 08-16 → default 10
9
└────────────┴───────────┴─────────────┘
10

11
Subquery 1: most recent change ≤ 08-16 → (1, 2019-08-14), (2, 2019-08-01)
12
  → product 1 → price=20, product 2 → price=50
13

14
Subquery 2: first change > 08-16 → (3, 2019-08-19)
15
  → product 3 → price=10 (default)
16

17
UNION merge:
18
┌────────────┬───────┐
19
│ product_id │ price │
20
├────────────┼───────┤
21
│ 1          │ 20    │
22
│ 2          │ 50    │
23
│ 3          │ 10    │
24
└────────────┴───────┘

1
SELECT ROUND(COUNT(m.player_id) / COUNT(DISTINCT a.player_id), 2) AS fraction
2
FROM Activity a
3
         LEFT JOIN (SELECT player_id, MIN(event_date) AS event_date
4
                    FROM Activity
5
                    GROUP BY player_id) m
6
                   ON a.player_id = m.player_id AND DATEDIFF(a.event_date, m.event_date) = 1;

1
Step 1: Subquery finds the first login date
2
  player_id=1 → 2016-03-01
3
  player_id=2 → 2017-06-25
4
  player_id=3 → 2016-03-02
5

6
Step 2: LEFT JOIN condition: same player + DATEDIFF=1 (login on the next day)
7
  Activity(1, 2016-03-02) JOIN m(1, 2016-03-01) → DATEDIFF=1 ✅ → m.player_id=1
8
  Activity(1, 2016-03-01) JOIN m(1, 2016-03-01) → DATEDIFF=0 ❌
9
  Activity(2, 2017-06-25) JOIN m(2, 2017-06-25) → DATEDIFF=0 ❌
10
  Activity(3, 2018-07-03) JOIN m(3, 2016-03-02) → DATEDIFF=488 ❌
11

12
Step 3: Compute the ratio
13
  COUNT(m.player_id) = 1 (only player 1 logged in the next day)
14
  COUNT(DISTINCT a.player_id) = 3 (total number of players)
15
  fraction = 1/3 = 0.33

1
SELECT w1.id
2
FROM Weather w1
3
         JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
4
WHERE w1.temperature > w2.temperature;

1
SELECT DISTINCT l1.Num AS ConsecutiveNums
2
FROM Logs l1,
3
     Logs l2,
4
     Logs l3
5
WHERE l1.Id = l2.Id - 1
6
  AND l2.Id = l3.Id - 1
7
  AND l1.Num = l2.Num
8
  AND l2.Num = l3.Num;

1
Logs table:
2
┌────┬─────┐
3
│ id │ num │
4
├────┼─────┤
5
│ 1  │ 1   │
6
│ 2  │ 1   │
7
│ 3  │ 1   │  ← 1 appears 3 times consecutively ✅
8
│ 4  │ 2   │
9
│ 5  │ 1   │
10
│ 6  │ 2   │
11
│ 7  │ 2   │  ← 2 only appears 2 times consecutively ❌
12
└────┴─────┘
13

14
Three-table self join condition: l1.id=l2.id-1 AND l2.id=l3.id-1
15
→ match: (id=1,num=1), (id=2,num=1), (id=3,num=1)
16
→ l1.Num=l2.Num=l3.Num=1 ✅
17
→ after DISTINCT result: ConsecutiveNums = 1

1
SELECT *
2
FROM Users
3
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode\.com$' COLLATE utf8mb4_bin;

1
^[a-zA-Z]                    → starts with a letter
2
[a-zA-Z0-9_.-]*              → followed by any number of letters/digits/underscores/dots/hyphens
3
@leetcode\.com$              → ends with @leetcode.com
4
COLLATE utf8mb4_bin          → case-sensitive (ensures the domain is lowercase)

1
SELECT *
2
FROM Patients
3
WHERE conditions LIKE '% DIAB1%'
4
   OR conditions LIKE 'DIAB1%';

1
SELECT user_id,
2
       CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) AS name
3
FROM Users
4
ORDER BY user_id;

1
WITH tab AS (SELECT pp.user_id, pi.category
2
             FROM ProductPurchases pp
3
                      JOIN ProductInfo pi ON pp.product_id = pi.product_id)
4
SELECT tab.category                AS category1,
5
       tab2.category               AS category2,
6
       COUNT(DISTINCT tab.user_id) AS customer_count
7
FROM tab
8
         JOIN tab AS tab2
9
              ON tab.user_id = tab2.user_id AND tab.category < tab2.category
10
GROUP BY tab.category, tab2.category
11
HAVING customer_count >= 3
12
ORDER BY customer_count DESC, category1, category2;

1
Step 1: CTE tab — join purchase records and product categories
2
┌─────────┬──────────┐
3
│ user_id │ category │
4
├─────────┼──────────┤
5
│ 1       │ A        │
6
│ 1       │ B        │
7
│ 2       │ A        │
8
│ 2       │ B        │
9
│ 3       │ A        │
10
│ 3       │ B        │
11
│ 4       │ A        │  ← only bought A, no B
12
└─────────┴──────────┘
13

14
Step 2: Self join to generate category pairs (condition category1 < category2 avoids duplicates)
15
tab JOIN tab ON same user_id AND tab.category < tab2.category
16
┌─────────┬───────────┬───────────┐
17
│ user_id │ category1 │ category2 │
18
├─────────┼───────────┼───────────┤
19
│ 1       │ A         │ B         │
20
│ 2       │ A         │ B         │
21
│ 3       │ A         │ B         │
22
│ 4       │ (no B)    │           │ ← no match
23
└─────────┴───────────┴───────────┘
24

25
Step 3: GROUP BY (category1, category2) + HAVING ≥ 3
26
┌───────────┬───────────┬────────────────┬──────┐
27
│ category1 │ category2 │ COUNT(DISTINCT)│ ≥ 3? │
28
├───────────┼───────────┼────────────────┼──────┤
29
│ A         │ B         │ 3              │ ✅   │
30
└───────────┴───────────┴────────────────┴──────┘

1
SELECT s.user_id,
2
       ROUND(IFNULL(SUM(IF(c.action = 'confirmed', 1, 0)) / COUNT(c.action), 0), 2)
3
           AS confirmation_rate
4
FROM Signups s
5
         LEFT JOIN Confirmations c ON s.user_id = c.user_id
6
GROUP BY s.user_id;

1
SELECT sample_id,
2
       dna_sequence,
3
       species,
4
       CASE WHEN dna_sequence LIKE 'ATG%' THEN 1 ELSE 0 END             AS has_start,
5
       CASE WHEN dna_sequence REGEXP 'TAA$|TAG$|TGA$' THEN 1 ELSE 0 END AS has_stop,
6
       CASE WHEN dna_sequence LIKE '%ATAT%' THEN 1 ELSE 0 END           AS has_atat,
7
       CASE WHEN dna_sequence LIKE '%GGG%' THEN 1 ELSE 0 END            AS has_ggg
8
FROM Samples;

1
SELECT tweet_id
2
FROM Tweets
3
WHERE LENGTH(content) > 15;

1
DELETE
2
p1 FROM Person p1
3
JOIN Person p2 ON p1.email = p2.email AND p1.id > p2.id;

1
Person table before delete:
2
┌────┬───────────┐
3
│ id │ email     │
4
├────┼───────────┤
5
│ 1  │ john@mail │  ← kept (smallest id)
6
│ 2  │ bob@mail  │
7
│ 3  │ john@mail │  ← deleted (same email, larger id)
8
└────┴───────────┘
9

10
Self join condition: p1.email = p2.email AND p1.id > p2.id
11
→ p1(3, john@mail) JOIN p2(1, john@mail): same email AND 3>1 ✅ → DELETE p1
12
→ p1(1, john@mail) JOIN p2(3, john@mail): 1>3 ❌ → not deleted
13
→ p1(2, bob@mail): no smaller id with same email → not deleted
14

15
After delete:
16
┌────┬───────────┐
17
│ id │ email     │
18
├────┼───────────┤
19
│ 1  │ john@mail │
20
│ 2  │ bob@mail  │
21
└────┴───────────┘