StrataScratch - SQL Problems (1)

StrataScratch Drop Box 인터뷰 문제 

 

Write a query that calculates the difference between the highest salaries found in the marketing and engineering departments. Output just the absolute difference in salaries.

 

마케팅 부서와 엔지니어링 부서에서 가장 높은 연봉의 차이를 계산하는 쿼리를 작성합니다. 

 

SELECT 
MAX(CASE WHEN b.department = 'marketing' THEN salary END) - 
MAX(CASE WHEN b.department = 'engineering' THEN salary END) AS max_salary_diff
FROM db_employee a
JOIN db_dept b
ON a.department_id = b.id

 

 

StrataScratch Microsoft 인터뷰 문제 

We have a table with employees and their salaries, however, some of the records are old and contain outdated salary information. Find the current salary of each employee assuming that salaries increase each year. Output their id, first name, last name, department ID, and current salary. Order your list by employee ID in ascending order.

직원들과 그들의 봉급에 대한 테이블이 있지만, 일부 기록은 오래되고 오래된 봉급 정보를 포함하고 있습니다. 매년 급여가 증가한다고 가정하고 각 직원의 현재 급여를 구한다. ID, 이름, 성, 부서 ID 및 현재 급여를 출력합니다. 직원 ID별로 오름차순으로 목록을 정렬합니다.

 

테이블 정보

easy 문제인데 어렵네요 ..... 

First_name	Last_name	Price	Department_id
Todd	Wilson	110000	1006
Todd	Wilson	106119	1006
Justin	Simon	128922	1005
Justin	Simon	130000	1005

 

SELECT 
    id,
    first_name,
    last_name,
    department_id,
    salary as max_salary
FROM (
    SELECT *, 
    dense_rank() OVER (PARTITION BY id ORDER BY salary DESC) AS rnk
    FROM ms_employee_salary) fnl
WHERE fnl.rnk = 1
ORDER by fnl.id ASC

 

문제 핵심 함수

DENSE_RANK() : 순위 값 중 동등 순위 번호는 같게 나오고 그 다음 순위를 출력 

 

PARTITION BY: SELECT 순위함수() OVER(PARTITION BY 컬럼명 ORDER BY 컬럼명) FROM 테이블명

                           SELECT 집계함수(컬럼명) OVER (PARTITION BY 컬럼명) FROM 테이블명

 

PARTITION BY를 사용하여 그룹으로 묶어서 연산을 할 수 있습니다.. GROUP BY 절을 사용하지 않고, 조회된 각 행에 그룹으로 집계된 값을 표시할 때 OVER 절과 함께 PARTITION BY 절을 사용

 

From 절에서 subquery를 사용하여 price에서 과거의 값과 큰 현재값의 순위를 구하고 where절 조건을 통해 현재 값을 갖고 오는것이 이 문제의 핵심 이였다고 생각합니다.