Katkat
Back

Calculate Net Pay Change for Q1 2023 Decreases

GustoData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.