[SQL 코드카타] 118~120번 (LeetCode)

118번 investments-in-2016

SELECT 
    ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE --1) 조건1 tiv_2015가 같은 tiv
    tiv_2015 IN (
                SELECT tiv_2015
                FROM Insurance
                GROUP BY tiv_2015
                HAVING COUNT(*) > 1
                )
    AND (lat, lon) IN ( --2) 조건2 lat과 lon이 서로 다른
                SELECT lat, lon
                FROM Insurance
                GROUP BY lat, lon
                HAVING COUNT(*) = 1
            )

> ? count 윈도우 함수 사용

select 
	ROUND(sum(tiv_2016), 2) tiv_2016
from (
    select *, 
        count(concat(lat, lon))over(partition by lat, lon order by lat, lon) numb,
        count(tiv_2015)over(partition by tiv_2015 order by tiv_2015) tf
    from Insurance
) a 
where 
    tf > 1 
    and numb = 1

119번 department-top-three-salaries

SELECT
    dp.name AS Department,
    ep.name AS Employee,
    ep.Salary
FROM (SELECT -- 1) 상위 3위인 봉급을 조회하기 위해 급여 순위 조회
        name,
        departmentID,
        salary,
        DENSE_RANK() over(partition by departmentID order by salary desc) as rn_salary
    FROM
        Employee
    ) ep
    join Department dp
    ON ep.departmentID = dp.id -- 2) 부서 이름을 가져오기 위해 조인
WHERE 
    rn_salary <= 3

 

120번 fix-names-in-a-table

> INITCAP()함수는 ORACLE에서만 제공

> UPPER() = UCASE() / LOWER() = LCASE()

SELECT --1) 문자열 자르기를 통해 실행
    user_id,
    CONCAT(UPPER(LEFT(name,1)),LOWER(SUBSTRING(name,2))) AS name
FROM
    Users
ORDER BY 
    user_id