Sales commission is paid per deal from commission_deals (deal_id, rep_id, amount, discount_pct, is_renewal). Finance wrote the policy as a list of rules. The first rule that applies decides the deal, and later rules are never consulted:
1. unpriced — the deal has no amount. It earns no commission: rate and commission are NULL. 2. over_discounted — the discount is more than 30%. Rate 0. A missing discount_pct means no discount. 3. renewal — the deal is a renewal. Rate 2%, whatever the amount. A missing is_renewal means it isn't one. 4. tier_3 — amount of 50,000 or more. Rate 10%. 5. tier_2 — amount of 10,000 or more. Rate 7%. 6. tier_1 — everything else. Rate 5%.
Return every deal with the rule that decided it, its rate as a fraction (e.g. 0.07) and commission = amount × rate, rounded to 2 decimal places.
Columns: deal_id, rule, rate, commission. Sort by deal_id.