SELECT query_name
, ROUND(AVG(rating / position), 2) AS quality
# == 4가지 방법 ==
, ROUND(COUNT(CASE WHEN rating < 3 THEN 1 END) / COUNT(*) * 100, 2) AS poor_query_percentage
, ROUND(SUM(IF(rating < 3, 1, 0)) / COUNT(*) * 100, 2) AS poor_query_percentage
, ROUND(SUM(rating < 3) / COUNT(*) * 100, 2) AS poor_query_percentage
, ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage
FROM Queries
GROUP BY query_name

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

SELECT (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(*) = 1
    ORDER BY num DESC LIMIT 1
) AS num;

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

DELETE FROM Person
WHERE id NOT IN (
    SELECT min_id
    FROM (
        SELECT MIN(id) AS min_id
        FROM Person
        GROUP BY email
    ) M
);

+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+

+--------------+
| subject_name |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+

| student_id | student_name | subject_name |
| ---------- | ------------ | ------------ |
| 1          | Alice        | Programming  |
| 1          | Alice        | Physics      |
| 1          | Alice        | Math         |
| 2          | Bob          | Programming  |
| 2          | Bob          | Physics      |
| 2          | Bob          | Math         |
| 13         | John         | Programming  |
| 13         | John         | Physics      |
| 13         | John         | Math         |
| 6          | Alex         | Programming  |
| 6          | Alex         | Physics      |
| 6          | Alex         | Math         |

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

SELECT ROUND(COUNT(player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id, event_date) IN (
    SELECT player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY)
    FROM Activity
    GROUP BY player_id
);

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

WITH C AS (
    SELECT visited_on, SUM(amount) AS day_amount 
    FROM Customer 
    GROUP BY visited_on
)

SELECT C1.visited_on, SUM(C2.day_amount) AS amount, ROUND(SUM(C2.day_amount) / 7, 2) AS average_amount
FROM C C1, C C2
WHERE 
    C1.visited_on >= DATE_ADD((SELECT MIN(visited_on) FROM Customer), INTERVAL 6 DAY)
    AND DATEDIFF(C1.visited_on, C2.visited_on) BETWEEN 0 AND 6 
GROUP BY C1.visited_on
ORDER BY C1.visited_on;

SELECT CAST('30일 이상' AS UNSIGNED); # 30

SELECT CONVERT('30일 이상', UNSIGNED); # 30

SELECT CAST('~ 30일 이상' AS UNSIGNED); # 0
SELECT CONVERT('~ 30일 이상', UNSIGNED); # 0

SELECT CAST('30일123 이상' AS UNSIGNED); # 30
SELECT CONVERT('30일123 이상', UNSIGNED); # 30

SELECT CONCAT(file_id, file_name, file_ext)) AS file_path
FROM USED_GOODS_FILE
WHERE board_id IN (
    SELECT board_id
    FROM USED_GOODS_BOARD
    ORDER BY views DESC
    LIMIT 5 # top 5
)
ORDER BY file_id DESC;

SELECT CONCAT(file_id, file_name, file_ext)) AS file_path
FROM USED_GOODS_FILE
WHERE board_id IN (
    SELECT *
    FROM (
        SELECT board_id
        FROM USED_GOODS_BOARD
        ORDER BY views DESC
        LIMIT 5 # top 5
    ) AS B
)
ORDER BY file_id DESC;

WHERE board_id = (
    SELECT board_id
    FROM USED_GOODS_BOARD
    ORDER BY views DESC
    LIMIT 1
)

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets

WITH RECURSIVE cte (n) AS
(
	-- Non-Recursive (첫번째 루프에서만 실행됨)
  SELECT 1
  UNION ALL
	-- Recursive (읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

SELECT * FROM Employees LEFT JOIN Salaries USING(employee_id)
UNION 
SELECT * FROM Employees RIGHT JOIN Salaries USING(employee_id)

| employee_id | name     | salary   |
| ----------- | -------- | -------- |
| 2           | Crew     | null     |
| 4           | Haven    | 63539    |
| 5           | Kristian | 76071    |
| 5           | 76071    | Kristian |
| 1           | 22517    | null     |
| 4           | 63539    | Haven    |

SELECT * FROM Employees LEFT JOIN Salaries USING(employee_id)

| employee_id | name     | salary |
| ----------- | -------- | ------ |
| 2           | Crew     | null   |
| 4           | Haven    | 63539  |
| 5           | Kristian | 76071  |

SELECT * FROM Employees RIGHT JOIN Salaries USING(employee_id)

| employee_id | salary | name     |
| ----------- | ------ | -------- |
| 5           | 76071  | Kristian |
| 1           | 22517  | null     |
| 4           | 63539  | Haven    |

SELECT employee_id, name, salary FROM Employees LEFT JOIN Salaries USING(employee_id)
UNION 
SELECT employee_id, name, salary FROM Employees RIGHT JOIN Salaries USING(employee_id)

| employee_id | name     | salary |
| ----------- | -------- | ------ |
| 2           | Crew     | null   |
| 4           | Haven    | 63539  |
| 5           | Kristian | 76071  |
| 1           | null     | 22517  |

SELECT product_name, SUM(unit) AS unit
FROM Orders
INNER JOIN Products USING(product_id)
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2020-02'
GROUP BY product_id
HAVING unit >= 100;

SELECT *
FROM Stadium
WHERE people >= 100

| id | visit_date | people |
| -- | ---------- | ------ |
| 2  | 2017-01-02 | 109    |
| 3  | 2017-01-03 | 150    |
| 5  | 2017-01-05 | 145    |
| 6  | 2017-01-06 | 1455   |
| 7  | 2017-01-07 | 199    |
| 8  | 2017-01-09 | 188    |

SELECT
    *,
    id - ROW_NUMBER() OVER (ORDER BY id) AS _group
FROM Stadium
WHERE people >= 100

| id | visit_date | people | _group |
| -- | ---------- | ------ | ------ |
| 2  | 2017-01-02 | 109    | 1      |
| 3  | 2017-01-03 | 150    | 1      |
| 5  | 2017-01-05 | 145    | 2      |
| 6  | 2017-01-06 | 1455   | 2      |
| 7  | 2017-01-07 | 199    | 2      |
| 8  | 2017-01-09 | 188    | 2      |

WITH T AS (
    SELECT
        *,
        id - ROW_NUMBER() OVER (ORDER BY id) AS _group
    FROM Stadium
    WHERE people >= 100
)

SELECT id, visit_date, people
FROM T
WHERE _group IN (
    SELECT _group
    FROM T
    GROUP BY _group
    HAVING COUNT(id) >= 3
);

| id | visit_date | people |
| -- | ---------- | ------ |
| 5  | 2017-01-05 | 145    |
| 6  | 2017-01-06 | 1455   |
| 7  | 2017-01-07 | 199    |
| 8  | 2017-01-09 | 188    |

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 1;

SELECT 
    # LEFT(trans_date, 7) AS month
    DATE_FORMAT(trans_date, '%Y-%m') AS month
    , country
    , COUNT(*) AS trans_count
    , SUM(IF(state = 'approved', 1, 0)) AS approved_count
    , SUM(amount) AS trans_total_amount
    , SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY country, month

SELECT COALESCE(NULL, NULL, NULL, 'hi~', NULL, 'bye');

# hi~

SELECT 
    S.student_id
    , S.student_name
    , Sub.subject_name
    , IFNULL(E.cnt, 0) AS attended_exams
...

SELECT
    S.student_id
    , S.student_name
    , Sub.subject_name
    , COALESCE(E.cnt, 0) AS attended_exams
...

SELECT column1, column2, ...
FROM table_name
WHERE columnN (NOT) LIKE pattern;

SELECT name FROM cities WHERE name RLIKE 'A|B|R';
-- or
SELECT name FROM cities WHERE name REGEXP 'A|B|R';
-- or using LIKE
SELECT name FROM cities WHERE name LIKE '%A%' OR name LIKE '%B%' OR name LIKE '%R%'

SELECT name FROM cities WHERE name RLIKE '^A|^B|^R';
-- or
SELECT name FROM cities WHERE name REGEXP '^A|^B|^R';
-- or using LIKE
SELECT name FROM cities WHERE name LIKE 'A%' OR name LIKE 'B%' OR name LIKE 'R%'

SELECT * FROM 테이블이름 LIMIT 10; # 처음부터 10개 row 출력하기 (1 ~ 10)

SELECT * FROM 테이블이름 LIMIT 20, 10; # 20번째 row부터 그 이후 10개 row 출력하기 (21 ~ 30)

SELECT * FROM 테이블이름 LIMIT 10 OFFSET 20;

SELECT (
    SELECT DISTINCT salary
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1, 1 # -- 1번째 row부터 그 이후 1개 row == 2번째 row
) AS SecondHighestSalary;

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

SELECT Q1.person_name
FROM Queue Q1, Queue Q2
WHERE Q1.turn >= Q2.turn
GROUP BY Q1.turn
HAVING SUM(Q2.weight) <= 1000
ORDER BY SUM(Q2.weight) 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 ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM (
    SELECT 
        tiv_2016
        , COUNT(*) OVER(PARTITION BY tiv_2015) AS cnt1
        , COUNT(*) OVER(PARTITION BY lat, lon) AS cnt2
    FROM Insurance
) AS T
WHERE cnt1 > 1 AND cnt2 = 1;

SELECT *,
  RANK() OVER (ORDER BY score DESC) AS _rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS _dense_rank,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS _row_number
FROM Scores;

| id | score | _rank | _dense_rank | _row_number |
| -- | ----- | ----- | ----------- | ----------- |
| 3  | 4     | 1     | 1           | 1           |
| 5  | 4     | 1     | 1           | 2           |
| 4  | 3.85  | 3     | 2           | 3           |
| 2  | 3.65  | 4     | 3           | 4           |
| 6  | 3.65  | 4     | 3           | 5           |
| 1  | 3.5   | 6     | 4           | 6           |

# -- DENSE_RANK()를 WHERE 조건으로 보고싶다면, SELECT 절에서 아예 CASE 문으로 처리
SELECT D.name AS Department, E.name AS Employee, E.salary AS Salary
FROM (
    SELECT *, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS _rank
    FROM Employee
) AS E
INNER JOIN Department D ON E.departmentId = D.id
WHERE E._rank <= 3;

SELECT D.name AS Department, E1.name AS Employee, E1.salary AS Salary
FROM Employee E1
INNER JOIN Department D ON E1.departmentId = D.id
WHERE (
    SELECT COUNT(DISTINCT E2.salary)
    FROM Employee E2
    WHERE E2.salary > E1.salary AND E2.departmentId = E1.departmentId
) < 3;  # -- 같은 department에서 자신의 salary 보다 높은 salary가 0~2개 존재해야 top3 임!

CAST(value AS datatype)

CONVERT(value, type)

CONVERT(value USING charset)

SELECT CustomerName, CONCAT(Address, " ", PostalCode, " ", City) AS Address
FROM Customers;

SELECT CustomerName, CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;

SUBSTRING(string, start, length)
SUBSTRING(string FROM start FOR length)

LEFT(string, number_of_chars)

RIGHT(string, number_of_chars)

SUBSTRING_INDEX(string, delimiter, number)

SELECT SUBSTRING_INDEX("www.naver.com", ".", 1); # www
SELECT SUBSTRING_INDEX("www.naver.com", ".", 2); # www.naver
SELECT SUBSTRING_INDEX("www.naver.com", ".", 3); # www.naver.com

SELECT SUBSTRING_INDEX("www.naver.com", ".", -1); # com
SELECT SUBSTRING_INDEX("www.naver.com", ".", -2); # naver.com

SELECT product_id, 'store1' AS store, store1 AS price   # -- UNION 시 first query에서 column name 결정하므로, 이후에는 alias 설정 안 해도 ok!
FROM Products
WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2', store2
FROM Products
WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3', store3
FROM Products
WHERE store3 IS NOT NULL;