SELECT product_id
FROM Products
WHERE low_fats = 'Y'
  AND recyclable = 'Y';

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

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

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

SELECT tweet_id
FROM Tweets
WHERE CHAR_LENGTH(content) > 15;

SELECT u.unique_id, e.name
FROM Employees e
LEFT JOIN EmployeeUNI u
  ON e.id = u.id;

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

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;

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

SELECT a1.machine_id,
       ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
FROM Activity a1
JOIN Activity a2
  ON a1.machine_id = a2.machine_id
 AND a1.process_id = a2.process_id
 AND a1.activity_type = 'start'
 AND a2.activity_type = 'end'
GROUP BY a1.machine_id;

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;

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;

SELECT m.name
FROM Employee e
JOIN Employee m
  ON e.managerId = m.id
GROUP BY m.id, m.name
HAVING COUNT(*) >= 5;

SELECT s.user_id,
       ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c
  ON s.user_id = c.user_id
GROUP BY s.user_id;

SELECT id, movie, description, rating
FROM Cinema
WHERE id % 2 = 1
  AND description <> 'boring'
ORDER BY rating DESC;

SELECT p.product_id,
       IFNULL(ROUND(SUM(u.units * p.price) / SUM(u.units), 2), 0) 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;

SELECT p.project_id,
       ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project p
JOIN Employee e
  ON p.employee_id = e.employee_id
GROUP BY p.project_id;

SELECT r.contest_id,
       ROUND(COUNT(DISTINCT r.user_id) * 100 / (SELECT COUNT(*) FROM Users), 2) AS percentage
FROM Register r
GROUP BY r.contest_id
ORDER BY percentage DESC, r.contest_id ASC;

SELECT query_name,
       ROUND(AVG(rating / position), 2) AS quality,
       ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY query_name;

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(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country;

SELECT ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) AS immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id, MIN(order_date)
    FROM Delivery
    GROUP BY customer_id
);

SELECT ROUND(COUNT(a2.player_id) / COUNT(a1.player_id), 2) AS fraction
FROM (
    SELECT player_id, MIN(event_date) AS first_login
    FROM Activity
    GROUP BY player_id
) a1
LEFT JOIN Activity a2
  ON a1.player_id = a2.player_id
 AND DATEDIFF(a2.event_date, a1.first_login) = 1;

SELECT teacher_id,
       COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id;

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;

SELECT product_id,
       year AS first_year,
       quantity,
       price
FROM Sales
WHERE (product_id, year) IN (
    SELECT product_id, MIN(year)
    FROM Sales
    GROUP BY product_id
);

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5;

SELECT user_id,
       COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;

SELECT MAX(num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(*) = 1
) t;

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

SELECT m.employee_id,
       m.name,
       COUNT(e.employee_id) AS reports_count,
       ROUND(AVG(e.age)) AS average_age
FROM Employees m
JOIN Employees e
  ON m.employee_id = e.reports_to
GROUP BY m.employee_id, m.name
ORDER BY m.employee_id;

SELECT employee_id, department_id
FROM Employee
WHERE primary_flag = 'Y'
   OR employee_id IN (
       SELECT employee_id
       FROM Employee
       GROUP BY employee_id
       HAVING COUNT(*) = 1
   );

SELECT x, y, z,
       CASE
           WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
           ELSE 'No'
       END AS triangle
FROM Triangle;

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2
  ON l2.id = l1.id + 1
JOIN Logs l3
  ON l3.id = l1.id + 2
WHERE l1.num = l2.num
  AND l2.num = l3.num;

SELECT p.product_id,
       IFNULL(x.new_price, 10) AS price
FROM (
    SELECT DISTINCT product_id
    FROM Products
) p
LEFT JOIN (
    SELECT product_id, new_price
    FROM Products
    WHERE (product_id, change_date) IN (
        SELECT product_id, MAX(change_date)
        FROM Products
        WHERE change_date <= '2019-08-16'
        GROUP BY product_id
    )
) x
  ON p.product_id = x.product_id;

SELECT person_name
FROM (
    SELECT person_name,
           SUM(weight) OVER (ORDER BY turn) AS total_weight
    FROM Queue
) t
WHERE total_weight <= 1000
ORDER BY total_weight DESC
LIMIT 1;

SELECT 'Low Salary' AS category,
       COUNT(*) AS accounts_count
FROM Accounts
WHERE income < 20000

UNION ALL

SELECT 'Average Salary' AS category,
       COUNT(*) AS accounts_count
FROM Accounts
WHERE income BETWEEN 20000 AND 50000

UNION ALL

SELECT 'High Salary' AS category,
       COUNT(*) AS accounts_count
FROM Accounts
WHERE income > 50000;

SELECT employee_id
FROM Employees
WHERE salary < 30000
  AND manager_id NOT IN (
      SELECT employee_id
      FROM Employees
  )
ORDER BY employee_id;

SELECT CASE
           WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM Seat) THEN id
           WHEN id % 2 = 1 THEN id + 1
           ELSE id - 1
       END AS id,
       student
FROM Seat
ORDER BY id;

(SELECT u.name AS results
 FROM MovieRating mr
 JOIN Users u
   ON mr.user_id = u.user_id
 GROUP BY mr.user_id, u.name
 ORDER BY COUNT(*) DESC, u.name ASC
 LIMIT 1)

UNION ALL

(SELECT m.title AS results
 FROM MovieRating mr
 JOIN Movies m
   ON mr.movie_id = m.movie_id
 WHERE mr.created_at BETWEEN '2020-02-01' AND '2020-02-29'
 GROUP BY mr.movie_id, m.title
 ORDER BY AVG(mr.rating) DESC, m.title ASC
 LIMIT 1);

WITH daily AS (
    SELECT visited_on,
           SUM(amount) AS amount
    FROM Customer
    GROUP BY visited_on
),
rolling AS (
    SELECT visited_on,
           SUM(amount) OVER (
               ORDER BY visited_on
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ) AS amount,
           ROUND(AVG(amount) OVER (
               ORDER BY visited_on
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ), 2) AS average_amount
    FROM daily
)
SELECT visited_on, amount, average_amount
FROM rolling
WHERE visited_on >= (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
    FROM daily
)
ORDER BY visited_on;

SELECT id,
       COUNT(*) AS num
FROM (
    SELECT requester_id AS id
    FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id
    FROM RequestAccepted
) t
GROUP BY id
ORDER BY num DESC
LIMIT 1;

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

SELECT d.name AS Department,
       e.name AS Employee,
       e.salary AS Salary
FROM Employee e
JOIN Department d
  ON e.departmentId = d.id
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employee e2
    WHERE e2.departmentId = e.departmentId
      AND e2.salary > e.salary
) < 3;

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

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions REGEXP '(^| )DIAB1';

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

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

SELECT sell_date,
       COUNT(DISTINCT product) AS num_sold,
       GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;

SELECT p.product_name,
       SUM(o.unit) AS unit
FROM Products p
JOIN Orders o
  ON p.product_id = o.product_id
WHERE o.order_date BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY p.product_id, p.product_name
HAVING SUM(o.unit) >= 100;

SELECT user_id, name, mail
FROM Users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@leetcode[.]com$';

SELECT a.*, b.col
FROM A a
LEFT JOIN B b
  ON a.id = b.id;

SELECT a.*
FROM A a
LEFT JOIN B b
  ON a.id = b.id
WHERE b.id IS NULL;

SELECT group_col, COUNT(*) AS cnt
FROM T
GROUP BY group_col
HAVING COUNT(*) >= 5;

SELECT
    SUM(status = 'ok') AS ok_count,
    ROUND(AVG(status = 'ok'), 2) AS ok_rate
FROM T;

SELECT *
FROM T
WHERE (group_id, sort_col) IN (
    SELECT group_id, MIN(sort_col)
    FROM T
    GROUP BY group_id
);

SELECT id,
       SUM(amount) OVER (ORDER BY id) AS running_amount
FROM T;

SELECT ...
FROM ...
JOIN ... ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

SELECT col1, col2
FROM table_name
WHERE condition
ORDER BY col1 DESC, col2 ASC
LIMIT 10 OFFSET 20;

SELECT DISTINCT user_id
FROM Orders;

SELECT *
FROM Products
WHERE price BETWEEN 10 AND 100;

SELECT *
FROM Users
WHERE name LIKE 'A%';

SELECT *
FROM Users
WHERE id IN (1, 2, 3);

WHERE col IS NULL
WHERE col IS NOT NULL

SELECT IFNULL(score, 0) AS score
FROM T;

SELECT COALESCE(col1, col2, 'default') AS value
FROM T;

SELECT a.id, b.name
FROM A a
JOIN B b
  ON a.id = b.a_id;

SELECT a.id, b.name
FROM A a
LEFT JOIN B b
  ON a.id = b.a_id;

SELECT a.*
FROM A a
LEFT JOIN B b
  ON a.id = b.a_id
WHERE b.a_id IS NULL;

SELECT s.student_id, sub.subject_name
FROM Students s
CROSS JOIN Subjects sub;

SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary,
       MAX(salary) AS max_salary,
       MIN(salary) AS min_salary,
       SUM(salary) AS total_salary
FROM Employee
GROUP BY department_id;

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

SELECT user_id,
       COUNT(DISTINCT product_id) AS product_count
FROM Orders
GROUP BY user_id;

SELECT IF(score >= 60, 'pass', 'fail') AS result
FROM Exam;

SELECT name,
       CASE
           WHEN income < 20000 THEN 'Low Salary'
           WHEN income <= 50000 THEN 'Average Salary'
           ELSE 'High Salary'
       END AS category
FROM Accounts;

SELECT
    COUNT(*) AS total_count,
    SUM(state = 'approved') AS approved_count,
    ROUND(AVG(state = 'approved'), 2) AS approved_rate,
    SUM(IF(state = 'approved', amount, 0)) AS approved_amount
FROM Transactions;

SELECT product_id
FROM Product
WHERE price > (
    SELECT AVG(price)
    FROM Product
);

SELECT customer_id
FROM Customer
WHERE product_key IN (
    SELECT product_key
    FROM Product
);

SELECT c.customer_id
FROM Customer c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

SELECT *
FROM Sales
WHERE (product_id, year) IN (
    SELECT product_id, MIN(year)
    FROM Sales
    GROUP BY product_id
);

WITH first_login AS (
    SELECT player_id, MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
)
SELECT *
FROM first_login;

SELECT name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK() OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM Employee;

SELECT department_id,
       name,
       salary,
       DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS rnk
FROM Employee;

SELECT turn,
       person_name,
       SUM(weight) OVER (ORDER BY turn) AS total_weight
FROM Queue;

SELECT id,
       num,
       LAG(num, 1) OVER (ORDER BY id) AS prev_num,
       LEAD(num, 1) OVER (ORDER BY id) AS next_num
FROM Logs;

SELECT visited_on,
       SUM(amount) OVER (
           ORDER BY visited_on
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS seven_day_amount
FROM DailySales;

SELECT CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS fixed_name
FROM Users;

SELECT sell_date,
       GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM Activities
GROUP BY sell_date;

SELECT *
FROM Users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@leetcode[.]com$';

SELECT *
FROM Patients
WHERE conditions REGEXP '(^| )DIAB1';

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month
FROM Transactions;

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

SELECT ROUND(AVG(score), 2) AS avg_score
FROM Exam;

SELECT requester_id AS id
FROM RequestAccepted
UNION ALL
SELECT accepter_id AS id
FROM RequestAccepted;

SELECT 'Low Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts
WHERE income < 20000
UNION ALL
SELECT 'Average Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts
WHERE income BETWEEN 20000 AND 50000
UNION ALL
SELECT 'High Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts
WHERE income > 50000;

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

UPDATE Employee
SET salary = salary * 1.1
WHERE department_id = 1;

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

SELECT d.name AS Department,
       e.name AS Employee,
       e.salary AS Salary
FROM Employee e
JOIN Department d
  ON e.departmentId = d.id
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employee e2
    WHERE e2.departmentId = e.departmentId
      AND e2.salary > e.salary
) < 3;

WITH ranked AS (
    SELECT d.name AS Department,
           e.name AS Employee,
           e.salary AS Salary,
           DENSE_RANK() OVER (
               PARTITION BY e.departmentId
               ORDER BY e.salary DESC
           ) AS rnk
    FROM Employee e
    JOIN Department d
      ON e.departmentId = d.id
)
SELECT Department, Employee, Salary
FROM ranked
WHERE rnk <= 3;

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

SELECT ROUND(COUNT(a2.player_id) / COUNT(a1.player_id), 2) AS fraction
FROM (
    SELECT player_id, MIN(event_date) AS first_login
    FROM Activity
    GROUP BY player_id
) a1
LEFT JOIN Activity a2
  ON a1.player_id = a2.player_id
 AND DATEDIFF(a2.event_date, a1.first_login) = 1;

SELECT p.product_id,
       IFNULL(ROUND(SUM(u.units * p.price) / SUM(u.units), 2), 0) 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;

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l2.id = l1.id + 1
JOIN Logs l3 ON l3.id = l1.id + 2
WHERE l1.num = l2.num
  AND l2.num = l3.num;

DATEDIFF(a, b)                  -- 相差天数
TIMESTAMPDIFF(DAY, a, b)        -- 按指定单位计算差值
DATE_FORMAT(date, '%Y-%m')      -- 格式化日期
DATE_ADD(date, INTERVAL 1 DAY)  -- 日期加法
DATE_SUB(date, INTERVAL 1 DAY)  -- 日期减法
YEAR(date)                      -- 年
MONTH(date)                     -- 月
DAY(date)                       -- 日

CHAR_LENGTH(str)                -- 字符数
LENGTH(str)                     -- 字节数
CONCAT(a, b)                    -- 拼接
LEFT(str, n)                    -- 左 n 个字符
RIGHT(str, n)                   -- 右 n 个字符
SUBSTRING(str, start, len)      -- 截取
UPPER(str)                      -- 大写
LOWER(str)                      -- 小写
TRIM(str)                       -- 去两端空格
REPLACE(str, a, b)              -- 替换
GROUP_CONCAT(str)               -- 分组拼接

IFNULL(a, b)                    -- a 是 NULL 时用 b
COALESCE(a, b, c)               -- 返回第一个非 NULL
IF(condition, a, b)             -- 二选一
CASE WHEN ... THEN ... END      -- 多条件

ROUND(x, 2)                     -- 四舍五入
CEIL(x)                         -- 向上取整
FLOOR(x)                        -- 向下取整
ABS(x)                          -- 绝对值
COUNT(*)                        -- 行数
COUNT(DISTINCT x)               -- 去重计数
SUM(x)                          -- 求和
AVG(x)                          -- 平均