技术需求：统计从Job level 5晋升至Job level 6的员工人数

Got it, let's break down how to count employees who moved up from Job Level 5 to Level 6 using your daily snapshot dataset. This is a common HR analytics task, and SQL is the go-to tool here—let's walk through it step by step:

Key Assumption #

First, I’m assuming your dataset has a unique employee_id field (critical for tracking individual employees over time—without this, we can’t reliably count unique promotions).

Step 1: Spot Promotion Events with Window Functions #

We’ll use the LAG() window function to grab each employee’s job level from the previous day. This lets us easily identify when someone’s level switches from 5 to 6.

WITH daily_level_tracking AS (
    SELECT
        employee_id,
        effective_date,
        job_level,
        -- Get the job level from the prior day for the same employee
        LAG(job_level) OVER (
            PARTITION BY employee_id 
            ORDER BY effective_date ASC
        ) AS previous_day_level
    FROM
        your_dataset_table_name
)
-- Grab distinct employees who had a 5→6 jump
SELECT DISTINCT employee_id
FROM daily_level_tracking
WHERE
    job_level = '6'
    AND previous_day_level = '5';

Step 2: Refine to Avoid Edge Cases #

This next version adds checks to exclude edge cases (like employees hired directly into Level 6, or one-off data blips):

WITH employee_level_history AS (
    SELECT
        employee_id,
        effective_date,
        job_level,
        hire_date,
        LAG(job_level) OVER (
            PARTITION BY employee_id 
            ORDER BY effective_date ASC
        ) AS previous_day_level,
        -- Get the employee's very first job level from the dataset
        FIRST_VALUE(job_level) OVER (
            PARTITION BY employee_id 
            ORDER BY effective_date ASC
        ) AS initial_job_level
    FROM
        your_dataset_table_name
)
-- Count unique promoted employees
SELECT COUNT(DISTINCT employee_id) AS total_promoted_employees
FROM employee_level_history
WHERE
    job_level = '6'
    AND previous_day_level = '5'
    AND initial_job_level = '5' -- Ensure they started at Level 5
    AND effective_date > hire_date; -- Skip any "promotions" that happened on hire day

Quick Notes to Avoid Mistakes #

• Deduplicate Snapshots: If there are multiple entries for the same employee on the same effective_date, deduplicate first (e.g., take the latest entry of the day) to avoid false positives.
• Date Order: Always sort effective_date in ascending order when using window functions for time-based tracking—this ensures you’re comparing days in the right sequence.
• Data Types: Make sure job_level is treated as a string or consistent numeric type (don’t mix '5' and 5, for example).

内容的提问来源于stack exchange，提问作者Kanupriya Dhiman