Net Payouts After Agency Fee

Problem

A freelancing platform pays contributors through agencies. Each agency charges a service fee whose rate is determined by the single largest payout inside that agency.

Table: `Payouts`

```text
+-------------+---------+
| Column | Type |
+-------------+---------+
| agency_id | int |
| worker_id | int |
| worker_name | varchar |
| amount | int |
+-------------+---------+
(agency_id, worker_id) is the primary key. amount is the gross payout for that worker.
```

The fee rate per agency depends on that agency's maximum amount:

- max amount below 1000 -> fee rate 0 (no fee)
- max amount from 1000 to 10000 inclusive -> fee rate 0.24
- max amount above 10000 -> fee rate 0.49

Return `agency_id`, `worker_id`, `worker_name`, and the net payout in a column named `amount`, computed as the gross amount times (1 minus the fee rate), rounded to the nearest whole number.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 10 rows with the columns agency_id, worker_id, worker_name, amount.

Starter code (SQL)

SELECT *
FROM Payouts;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium