Your question is Dynamic Fee Reconciliation in Sheets. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Create a dynamic reconciliation model in Google Sheets that highlights anomalies where the payment processor fee deviates from the contract rate at Rippling.
Write a PostgreSQL query that identifies payment records with fee mismatches, missing fees, or no applicable contract rate.
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Rippling customer account identifier |
| account_name | VARCHAR(120) | Rippling customer account name |
| Column | Type | Description |
|---|---|---|
| contract_idPK | INT | Payment processor contract identifier |
| account_id | INT | Rippling account covered by the contract |
| processor | VARCHAR(40) | Payment processor named in the contract |
| contract_rate_pct | DECIMAL(6,3) | Contract fee percentage |
| effective_from | DATE | Contract start date |
| effective_to | DATE | Contract end date, null for an active contract |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Payment transaction identifier |
| account_id | INT | Rippling account associated with the payment |
| processor | VARCHAR(40) | Processor that handled the payment |
| payment_date | DATE | Date the payment was processed |
| gross_amount | DECIMAL(12,2) | Payment amount before processor fees |
| processor_fee | DECIMAL(12,2) | Fee charged by the payment processor |