Calculate Net Pay Change for Q1 2023 Decreases
Company: Gusto
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
EMPLOYEES
+-------------+------------+-------+
| employee_id | hire_date | state |
+-------------+------------+-------+
| 101 | 2022-04-01 | CA |
| 102 | 2021-11-12 | NY |
| 103 | 2020-07-20 | TX |
+-------------+------------+-------+
PAYROLL_TRANSACTIONS
+----------------+-------------+------------+-----------+------------+
| transaction_id | employee_id | paid_at | gross_pay | deductions |
+----------------+-------------+------------+-----------+------------+
| 1 | 101 | 2023-01-15 | 5000 | 500 |
| 2 | 101 | 2023-02-15 | 5000 | 550 |
| 3 | 102 | 2023-01-15 | 4500 | 400 |
| 4 | 103 | 2023-01-15 | 6000 | 600 |
| 5 | 102 | 2023-02-15 | 4500 | 450 |
+----------------+-------------+------------+-----------+------------+
##### Scenario
Online SQL screen evaluating day-to-day data-manipulation skills on Gusto payroll data
##### Question
Write a SQL query that returns each employee’s total net pay (gross_pay – deductions) for the first calendar quarter of 2023. For every employee, show the percentage change in net pay between their two most recent pay periods and filter to those whose net pay decreased.
##### Hints
Window functions or self-joins can help with pay-period comparisons.
Overview: This question evaluates a candidate's data-manipulation competency in SQL and Python, focusing on computing net pay, aggregating over a calendar quarter, and measuring percentage change between recent pay periods.
Write a SQL query that, for the first calendar quarter of 2023 (2023-01-01 to 2023-03-31), returns each employee’s total net pay (gross_pay − deductions). For those employees who have at least two pay periods in Q1 2023, compute the percentage change in net pay between their two most recent Q1 pay periods and return only the employees whose most recent net pay decreased compared to the previous one.
Tables
EMPLOYEES(employee_id INTEGER, hire_date DATE, state VARCHAR(2))
PAYROLL_TRANSACTIONS(transaction_id INTEGER, employee_id INTEGER, paid_at DATE, gross_pay DECIMAL(10,2), deductions DECIMAL(10,2))
Hints
- Filter paid_at to Q1 2023 (2023-01-01 through 2023-03-31).
- Compute net pay as gross_pay minus deductions before aggregating.