𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: 
You have 2 minutes to solve this SQL query.

Q: Find the employee(s) who received the highest salary increment compared to their previous salary.

Assume the table structure: 
salary_history(employee_id, salary, effective_date)

𝗠𝗲: Challenge accepted! 💪

WITH salary_changes AS (
    SELECT
        employee_id,
        salary,
        effective_date,
        salary - LAG(salary) OVER (
            PARTITION BY employee_id
            ORDER BY effective_date
        ) AS salary_increment
    FROM salary_history
)
SELECT
    employee_id,
    salary_increment
FROM (
    SELECT
        employee_id,
        salary_increment,
        DENSE_RANK() OVER (
            ORDER BY salary_increment DESC
        ) AS rnk
    FROM salary_changes
    WHERE salary_increment IS NOT NULL
) ranked
WHERE rnk = 1;

💡 Explanation: 
This query calculates each employee's salary increment and then finds the highest increment across all employees.

• LAG(salary) retrieves the employee's previous salary
• The difference between the current and previous salary gives the increment
• DENSE_RANK() ranks increments from highest to lowest
• The outer query returns all employees tied for the highest salary increment

This question tests your understanding of: 
✅ LAG() Window Function 
✅ Common Table Expressions (CTEs) 
✅ DENSE_RANK() 
✅ Time-Series Data Analysis

🎯 Expected Output Example

Employee ID | Salary Increment 
101 | 20,000 
205 | 20,000 

Both employees received the largest salary increase.

🚀 Why Interviewers Ask This? 
This is a classic window function interview question. It evaluates your ability to compare a row with its previous row—a common requirement in payroll, finance, and audit systems.

🚀 Tip for SQL Job Seekers: 
Master these analytical window functions: 
LAG() / LEAD() / FIRST_VALUE() / LAST_VALUE() / NTILE() 

These functions are frequently tested in product-based companies and data-focused interviews because they simplify complex row-by-row comparisons.

❤️ React with ❤️ for more interview challenges!