Calculate Net Pay Change for Q1 2023 Decreases

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.

Employees with Decreasing Q1 2023 Net Pay

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.

Table EMPLOYEES

column	type	constraints
employee_id	INTEGER	PRIMARY KEY, NOT NULL
hire_date	DATE	NOT NULL
state	VARCHAR(2)	NOT NULL

Sample data:

employee_id	hire_date	state
101	2022-04-01	CA
102	2021-11-12	NY
103	2020-07-20	TX

Table PAYROLL_TRANSACTIONS

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
employee_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES EMPLOYEES(employee_id)
paid_at	DATE	NOT NULL
gross_pay	DECIMAL(10,2)	NOT NULL
deductions	DECIMAL(10,2)	NOT NULL

Sample data:

deductions	employee_id	gross_pay	paid_at	transaction_id
500	101	5000	2023-01-15	1
550	101	5000	2023-02-15	2
400	102	4500	2023-01-15	3
600	103	6000	2023-01-15	4
450	102	4500	2023-02-15	5

Expected output

Employees who have at least two pay periods in Q1 2023 (2023-01-01 to 2023-03-31) and whose most recent Q1 net pay decreased compared to the previous Q1 pay period. For each such employee, return their employee_id, total Q1 net pay, the previous and current (most recent) net pay amounts, and the percentage change between them.

curr_net_pay	employee_id	pct_change	prev_net_pay	total_q1_net_pay
4450	101	-1.11	4500	8950
4050	102	-1.22	4100	8150

Hints

1. Filter paid_at to Q1 2023 (2023-01-01 through 2023-03-31).
2. Compute net pay as gross_pay minus deductions before aggregating.
3. Use window functions such as ROW_NUMBER or LAG, or a self-join, to compare each employee’s two most recent Q1 pay periods.