데이터리안 - SQL 데이터 분석캠프 실전반- 4주차: 추가연습문제

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반'을 수강하며 작성한 내용입니다.

184. Department Highest Salary

SELECT t.department
     , t.employee
     , t.salary
FROM (
     SELECT d.name AS department 
          , e.name AS employee
          , e.salary
          , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC ) AS dr
     FROM employee e
     INNER JOIN department d ON e.departmentid = d.id
) t
WHERE dr = 1

185. Department Top Three Salaries

SELECT t.department
     , t.employee
     , t.salary
FROM (
    SELECT d.name AS department
        , e.name AS employee
        , e.salary 
        , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr
    FROM employee e
        INNER JOIN department d ON e.departmentid = d.id
) t
WHERE dr <= 3

178. Rank Scores

SELECT s1.Score , 
(SELECT COUNT(DISTINCT score) from Scores s2 where s1.score<=s2.score) as `Rank`
from Scores s1
order by s1.score desc;

1321. Restaurant Growth

SELECT
    visited_on,
    (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount,
    ROUND(
        (
            SELECT SUM(amount) / 7
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        ),
        2
    ) AS average_amount
FROM customer c
WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )
GROUP BY visited_on

https://school.programmers.co.kr/learn/courses/30/lessons/131123

SELECT FOOD_TYPE
     , REST_ID
     , REST_NAME
     , FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN
    (SELECT FOOD_TYPE, MAX(FAVORITES)
     FROM REST_INFO
     GROUP BY FOOD_TYPE
    ) 
ORDER BY FOOD_TYPE DESC

https://school.programmers.co.kr/learn/courses/30/lessons/164668

SELECT uu.USER_ID
     , uu.NICKNAME
     , SUM(ub.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD ub
    INNER JOIN USED_GOODS_USER uu ON ub.writer_id = uu.user_id
WHERE STATUS = 'DONE'
GROUP BY uu.USER_ID, uu.NICKNAME
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES