SQL小结

-- find the highest salary per department
SELECT 
    department_name,
    first_name,
    last_name,
    salary
FROM 
    (
        SELECT 
            department_name,
            ROW_NUMBER() OVER (
                PARTITION BY department_name
                ORDER BY salary DESC) row_num, 
            first_name, 
            last_name, 
            salary
        FROM 
            employees e
            INNER JOIN departments d 
                ON d.department_id = e.department_id
    ) t
WHERE 
    row_num = 1;

SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM Scores

SELECT score, RANK() OVER (ORDER BY score DESC) AS rank
FROM Scores

SELECT s.score, l.rank 
from Scores s
LEFT JOIN (
    SELECT score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
    FROM Scores 
    GROUP BY score
) l on l.score=s.score
ORDER BY s.score DESC

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.id = l2.id - 1
    AND l2.id = l3.id - 1
    AND l1.num = l2.num
    AND l2.num = l3.num
;

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

-- CTE
WITH newlog AS (
SELECT id, num,
LAG(num) OVER (ORDER BY id) AS lag_num,
LEAD(num) OVER (ORDER BY id) AS lead_num
from logs
)
SELECT DISTINCT num AS ConsecutiveNums FROM newlog
WHERE lag_num=num AND lead_num=num

SELECT DISTINCT num ConsecutiveNums
FROM (
	SELECT *,
	CASE WHEN num = LAG(num) OVER(ORDER BY id ASC) AND num = LEAD(num) OVER(ORDER BY id ASC)
	THEN "Y"
	ELSE "N"
	END CNs
	FROM Logs
	) ConsNums
WHERE ConsNums.CNs = "Y";

SELECT t3.author_id, author_tb.author_level, t3.days_cnt
FROM (
    SELECT author_id, start_date, COUNT(*) AS days_cnt
    FROM (
        SELECT DISTINCT answer_date, author_id, DATE_SUB(answer_date, INTERVAL num DAY) AS start_date
        FROM (
            SELECT answer_date, author_id, DENSE_RANK() OVER(PARTITION BY author_id ORDER BY answer_date) AS num
            FROM answer_tb
        ) AS t1
    ) AS t2
    GROUP BY author_id, start_date
    HAVING days_cnt >= 3
) AS t3
LEFT JOIN author_tb ON t3.author_id=author_tb.author_id
ORDER BY t3.author_id

WITH cte AS(
    SELECT dept_emp.emp_no, dept_emp.dept_no, salaries.salary
    FROM dept_emp INNER JOIN salaries ON dept_emp.emp_no=salaries.emp_no
)

SELECT dept_no, emp_no, salary AS maxSalary
FROM cte
WHERE (dept_no, salary) IN
(
    SELECT dept_no, MAX(salary) AS salary
    FROM cte
    GROUP BY dept_no
)
ORDER BY dept_no

WITH cte AS(
    SELECT dept_emp.emp_no, dept_emp.dept_no, salaries.salary
    FROM dept_emp INNER JOIN salaries ON dept_emp.emp_no=salaries.emp_no
)

SELECT dept_no, emp_no, salary AS maxSalary
FROM
(
    SELECT *, RANK() OVER(PARTITION BY dept_no 
                          ORDER BY salary DESC) AS rank_within_dept
    FROM cte
) AS t
WHERE rank_within_dept = 1
ORDER BY dept_no

SELECT id, LENGTH(string) - LENGTH(REPLACE(string, ",", "")) AS cnt
FROM strings;

SELECT s1.emp_no, s1.salary, SUM(s2.salary)
FROM salaries s1 JOIN salaries s2 ON s1.emp_no >= s2.emp_no
WHERE s1.to_date = '9999-01-01' AND s2.to_date = '9999-01-01'
GROUP BY s1.emp_no, s1.salary

WITH act AS (
    SELECT user_id, course_id, in_datetime AS dt, 1 AS act
    FROM attend_tb
    UNION
    SELECT user_id, course_id, out_datetime AS dt, -1 AS act
    FROM attend_tb
)

SELECT c.course_id, c.course_name, MAX(t.max_num) AS max_num
FROM (
    SELECT course_id, SUM(act) OVER(PARTITION BY course_id ORDER BY dt) AS max_num
    FROM act
) AS t
LEFT JOIN course_tb AS c
ON t.course_id = c.course_id
GROUP BY c.course_id, c.course_name

WITH combined AS (
    SELECT u.*, v.tag
    FROM tb_user_video_log u
    JOIN tb_video_info v on u.video_id = v.video_id
),
grouped AS (
    SELECT tag, DATE_FORMAT(start_time, '%Y-%m-%d') AS dt, 
           SUM(if_like) AS like_cnt, SUM(if_retweet) AS retweet_cnt
    FROM combined
    GROUP BY tag, DATE_FORMAT(start_time, '%Y-%m-%d')
)
SELECT g1.tag, 
       g1.dt, 
       SUM(g2.like_cnt) sum_like_cnt_7d, 
       MAX(g2.retweet_cnt) max_retweet_cnt_7d
FROM grouped g1
JOIN grouped g2 
ON g1.tag = g2.tag
   AND g1.dt <= DATE_ADD(g2.dt, INTERVAL 6 DAY) 
   AND g1.dt >= g2.dt
WHERE g1.dt >= '2021-10-01' AND g1.dt <= '2021-10-03'
GROUP BY g1.tag, g1.dt