Commission Calculation Verification

MediumSQL · PostgreSQL

Problem

What steps do you take to verify the integrity and accuracy of a commission calculation before sending it to payroll at Adobe?

Using the provided Adobe commission tables, write one PostgreSQL query that validates active representatives' April 2025 commission records. Include representatives with no eligible deals so the output also exposes missing activity.

Output

One row per active representative, ordered by rep_name, then rep_id.
Return rep_name, eligible_deal_count, eligible_sales, expected_commission, recorded_commission, variance, and verification_status.
Include closed deals dated in April 2025 only. Flag missing rates, missing recorded commissions, negative amounts, mismatched totals, and representatives with no eligible deals.

Schema

representatives

Column	Type	Description
rep_idPK	INT	Unique sales representative identifier
rep_name	VARCHAR(100)	Sales representative name
plan	VARCHAR(30)	Commission plan assigned to the representative
is_active	BOOLEAN	Whether the representative is active for payroll processing

commission_deals

Column	Type	Description
deal_idPK	INT	Unique deal identifier
rep_id	INT	Representative responsible for the deal
close_date	DATE	Deal close date
deal_status	VARCHAR(20)	Deal lifecycle status
amount	NUMERIC(12,2)	Commissionable deal amount
recorded_commission	NUMERIC(12,2)	Commission currently recorded for payroll

commission_rates

Column	Type	Description
planPK	VARCHAR(30)	Commission plan name
commission_rate	NUMERIC(5,4)	Commission rate as a decimal fraction

Tablesrepresentativescommission_dealscommission_rates