Welcome to Subscribe On Youtube

3580. Find Consistently Improving Employees

Description

Table: employees

+-++
\| employee_id \| int     \|
\| name        \| varchar \|
+-+-+
\| employee_id \| name           \|
+-+-+

performance_reviews table:

+--+-+-+--+
\| 1         \| 1           \| 2023-01-15  \| 2      \|
\| 2         \| 1           \| 2023-04-15  \| 3      \|
\| 3         \| 1           \| 2023-07-15  \| 4      \|
\| 4         \| 1           \| 2023-10-15  \| 5      \|
\| 5         \| 2           \| 2023-02-01  \| 3      \|
\| 6         \| 2           \| 2023-05-01  \| 2      \|
\| 7         \| 2           \| 2023-08-01  \| 4      \|
\| 8         \| 2           \| 2023-11-01  \| 5      \|
\| 9         \| 3           \| 2023-03-10  \| 1      \|
\| 10        \| 3           \| 2023-06-10  \| 2      \|
\| 11        \| 3           \| 2023-09-10  \| 3      \|
\| 12        \| 3           \| 2023-12-10  \| 4      \|
\| 13        \| 4           \| 2023-01-20  \| 4      \|
\| 14        \| 4           \| 2023-04-20  \| 4      \|
\| 15        \| 4           \| 2023-07-20  \| 4      \|
\| 16        \| 5           \| 2023-02-15  \| 3      \|
\| 17        \| 5           \| 2023-05-15  \| 2      \|
+-+-+
\| employee_id \| name           \| improvement_score \|
+-+-+-+

Explanation:

  • Alice Johnson (employee_id = 1):
    • Has 4 reviews with ratings: 2, 3, 4, 5
    • Last 3 reviews (by date): 2023-04-15 (3), 2023-07-15 (4), 2023-10-15 (5)
    • Ratings are strictly increasing: 3 → 4 → 5
    • Improvement score: 5 - 3 = 2
  • Carol Davis (employee_id = 3):
    • Has 4 reviews with ratings: 1, 2, 3, 4
    • Last 3 reviews (by date): 2023-06-10 (2), 2023-09-10 (3), 2023-12-10 (4)
    • Ratings are strictly increasing: 2 → 3 → 4
    • Improvement score: 4 - 2 = 2
  • Bob Smith (employee_id = 2):
    • Has 4 reviews with ratings: 3, 2, 4, 5
    • Last 3 reviews (by date): 2023-05-01 (2), 2023-08-01 (4), 2023-11-01 (5)
    • Ratings are strictly increasing: 2 → 4 → 5
    • Improvement score: 5 - 2 = 3
  • Employees not included:
    • David Wilson (employee_id = 4): Last 3 reviews are all 4 (no improvement)
    • Emma Brown (employee_id = 5): Only has 2 reviews (needs at least 3)

The output table is ordered by improvement_score in descending order, then by name in ascending order.

</div>

Solutions

Solution 1: Using Window Functions and Aggregate Functions

First, we extract the most recent three performance review records for each employee and calculate the difference in rating between each review and the previous one. Next, we filter out employees whose ratings are strictly increasing, and compute their improvement score (i.e., the last rating minus the first rating among the last three reviews). Finally, we sort the results by improvement score in descending order and by name in ascending order.

  • import pandas as pd


def find_consistently_improving_employees(
    employees: pd.DataFrame, performance_reviews: pd.DataFrame
) -> pd.DataFrame:
    performance_reviews = performance_reviews.sort_values(
        ["employee_id", "review_date"], ascending=[True, False]
    )
    performance_reviews["rn"] = (
        performance_reviews.groupby("employee_id").cumcount() + 1
    )
    performance_reviews["lag_rating"] = performance_reviews.groupby("employee_id")[
        "rating"
    ].shift(1)
    performance_reviews["delta"] = (
        performance_reviews["lag_rating"] - performance_reviews["rating"]
    )
    recent = performance_reviews[
        (performance_reviews["rn"] > 1) & (performance_reviews["rn"] <= 3)
    ]
    improvement = (
        recent.groupby("employee_id")
        .agg(
            improvement_score=("delta", "sum"),
            count=("delta", "count"),
            min_delta=("delta", "min"),
        )
        .reset_index()
    )
    improvement = improvement[
        (improvement["count"] == 2) & (improvement["min_delta"] > 0)
    ]
    result = improvement.merge(employees[["employee_id", "name"]], on="employee_id")
    result = result.sort_values(
        by=["improvement_score", "name"], ascending=[False, True]
    )
    return result[["employee_id", "name", "improvement_score"]]


  • WITH
    recent AS (
        SELECT
            employee_id,
            review_date,
            ROW_NUMBER() OVER (
                PARTITION BY employee_id
                ORDER BY review_date DESC
            ) AS rn,
            (
                LAG(rating) OVER (
                    PARTITION BY employee_id
                    ORDER BY review_date DESC
                ) - rating
            ) AS delta
        FROM performance_reviews
    )
SELECT
    employee_id,
    name,
    SUM(delta) AS improvement_score
FROM
    recent
    JOIN employees USING (employee_id)
WHERE rn > 1 AND rn <= 3
GROUP BY 1
HAVING COUNT(*) = 2 AND MIN(delta) > 0
ORDER BY 3 DESC, 2;

All Problems

All Solutions