US and California Payroll Calculations

MediumSQL · PostgreSQL

Problem

Explain payroll calculations in US and California.

Using the provided employee, payroll, and tax-rate data, write a PostgreSQL query that calculates payroll for the January 31, 2025 pay period. Include US employees, apply federal tax to all employees, and apply the applicable state rate, including California.

Output

One row per US employee
Columns: employee_id, employee_name, state_code, gross_pay, pre_tax_deductions, federal_tax, state_tax, and net_pay
Include employees without payroll for the period with zero payroll amounts
Order by employee_id ascending

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
country_code	VARCHAR(2)	Employee country code
state_code	VARCHAR(2)	US state code

payroll_entries

Column	Type	Description
payroll_entry_idPK	INT	Unique payroll entry identifier
employee_id	INT	Employee receiving the payroll entry
pay_period_end	DATE	End date of the pay period
gross_pay	DECIMAL(12,2)	Gross earnings for the entry
pre_tax_deductions	DECIMAL(12,2)	Deductions made before tax calculation

tax_rates

Column	Type	Description
tax_rate_idPK	INT	Unique tax-rate identifier
jurisdiction_code	VARCHAR(10)	US or state jurisdiction code
tax_type	VARCHAR(20)	Federal or state tax category
rate	DECIMAL(8,6)	Tax rate expressed as a decimal
effective_from	DATE	Beginning date for the rate
effective_to	DATE	Ending date for the rate

Tablesemployeespayroll_entriestax_rates