SQL for Indirect Tax Calculations

HardSQL · PostgreSQL

Problem

Write a SQL query to calculate indirect taxation amounts and related calculations from transactional data.

Use posted transactions only. Apply the tax rate effective on each transaction date, account for valid customer exemptions, and preserve transactions without matching jurisdiction or rate data.

Output

One row per posted transaction, ordered by transaction date and transaction ID
Include transaction ID, jurisdiction name, gross amount, exempt amount, taxable amount, state tax, local tax, total tax, tax as a percentage of gross amount, prior transaction tax, monthly jurisdiction tax, and cumulative jurisdiction tax
Exclude void and pending transactions; retain NULL jurisdiction names and zero tax where no applicable rate exists

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
jurisdiction_id	INT	Tax jurisdiction identifier
transaction_date	DATE	Transaction date
taxable_amount	DECIMAL(12,2)	Gross transaction amount before exemption
transaction_type	VARCHAR(20)	Sale or refund classification
exemption_code	VARCHAR(20)	Customer exemption code
status	VARCHAR(20)	Transaction processing status

tax_rates

Column	Type	Description
tax_rate_idPK	INT	Unique tax rate identifier
jurisdiction_id	INT	Jurisdiction using the rate
effective_from	DATE	Rate start date
effective_to	DATE	Rate end date
state_rate	DECIMAL(8,5)	State tax rate as a decimal
local_rate	DECIMAL(8,5)	Local tax rate as a decimal

jurisdictions

Column	Type	Description
jurisdiction_idPK	INT	Unique jurisdiction identifier
jurisdiction_name	VARCHAR(100)	Tax jurisdiction name
country_code	VARCHAR(2)	Country code

tax_exemptions

Column	Type	Description
exemption_idPK	INT	Unique exemption record identifier
customer_id	INT	Customer receiving the exemption
exemption_code	VARCHAR(20)	Exemption code
exemption_pct	DECIMAL(5,4)	Exempt percentage as a decimal
valid_from	DATE	Exemption start date
valid_to	DATE	Exemption end date

Tablestransactionstax_ratesjurisdictionstax_exemptions