3580. Find Consistently Improving Employees

Tables

erDiagram
	employees {
		employee_id int PK
		name varchar
	}
	performance_reviews {
		review_id int PK
		employee_id int FK
		review_date date
		rating int "1 ~ 5"
	}
	employees || -- o{ performance_reviews : review
Mermaid
복사

Question

Write a solution to find employees who have consistently improved their performance over their last three reviews.

•

An employee must have at least 3 review to be considered

•

The employee's last 3 reviews must show strictly increasing ratings (each review better than the previous)

•

Use the most recent 3 reviews based on review_date for each employee

•

Calculate the improvement score as the difference between the latest rating and the earliest rating among the last 3 reviews

Return the result table ordered by improvement score in descending order, then by name in ascending order.

The result format is in the following example.

DataFlow

•

at least 3 review -> where latest 3 reviews -> group by employee_id -> count review_id

•

reting increasing

•

diff first, last

Answer

WITH review_rn AS (
	SELECT
		*,
		COUNT(review_id) OVER(PARTITION BY employee_id) AS cnt,
		ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY review_date DESC) AS rn
	FROM performance_reviews
),

review_filter AS (
	SELECT
		*
	FROM review_rn
	WHERE cnt >= 3
	AND rn <= 3
),

review_pivot AS (
	SELECT
		employee_id,
		MAX(CASE WHEN rn = 3 THEN rating END) AS one,
		MAX(CASE WHEN rn = 2 THEN rating END) AS two,
		MAX(CASE WHEN rn = 1 THEN rating END) AS three
	FROM review_filter
	GROUP BY employee_id
),

review_diff AS (
	SELECT
		*,
		(three - one) AS diff
	FROM review_pivot
	WHERE one < two
	AND two < three
)

SELECT
	d.employee_id,
	e.name,
	d.diff AS improvement_score
FROM review_diff d
INNER JOIN employees e
	ON d.employee_id = e.employee_id
ORDER BY d.diff DESC, e.name ASC;
SQL
복사

가독성을 위해서 모두 CTE로 작성

•

subquery 작성

•

비용이 작은 쿼리로 수정이 가능한 부분?

SELECT
	d.employee_id,
	e.name,
	d.diff AS improvement_score
FROM (
	SELECT
		*,
		(three - one) AS diff
	FROM (
		SELECT
			employee_id,
			MAX(CASE WHEN rn = 3 THEN rating END) AS one,
			MAX(CASE WHEN rn = 2 THEN rating END) AS two,
			MAX(CASE WHEN rn = 1 THEN rating END) AS three
		FROM (
			SELECT
				*
			FROM (
				SELECT
					*,
					COUNT(review_id) OVER(PARTITION BY employee_id) AS cnt,
					ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY review_date DESC) AS rn
				FROM performance_reviews
			) review_rn
			WHERE cnt >= 3
			AND rn <= 3
		) review_filter
		GROUP BY employee_id
	) review_pivot
	WHERE one < two
	AND two < three
) d
INNER JOIN employees e
	ON d.employee_id = e.employee_id
ORDER BY d.diff DESC, e.name ASC;
SQL
복사

subquery 치환 속도 변환 없음

WITH ranking AS (
	SELECT
		pr.employee_id,
		e.name,
		pr.rating,
		RANK() OVER (PARTITION BY pr.employee_id ORDER BY pr.review_date DESC) AS ranks
	FROM performance_reviews pr
	JOIN employees e
		ON pr.employee_id = e.employee_id
),

pivoted AS (
	SELECT
		employee_id,
		name,
		MAX(CASE WHEN ranks = 1 THEN rating END) AS latest_rating,
		MAX(CASE WHEN ranks = 2 THEN rating END) AS sec_rating,
		MAX(CASE WHEN ranks = 3 THEN rating END) AS third_latest_rating
	FROM ranking
	GROUP BY employee_id, name
)

SELECT
	employee_id,
	name,
	latest_rating - third_latest_rating AS improvement_score
FROM pivoted
WHERE latest_rating IS NOT NULL
AND third_latest_rating IS NOT NULL
AND latest_rating > third_latest_rating
AND sec_rating > third_latest_rating
ORDER BY improvement_score DESC, name;
SQL
복사

in not null 사용 -> count 함수를 대체