Broker Commission by Deal Slab — SQL Problem
A brokerage pays its agents by slab. On a rent deal the commission is one month's rent (deal_value is the monthly rent).
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #353
Problem statement
A brokerage pays its agents by slab. On a rent deal the commission is one month's rent (deal_value is the monthly rent). On a sale the commission is 2% of the sale price when the price is below ₹1 crore (₹1,00,00,000) and 1.5% when it is ₹1 crore or more.
Return deal_id, broker and commission (in rupees) for every deal, in any order.
Tables
Table: Deal
| Column | Type |
|---|---|
| deal_id | int |
| broker | varchar |
| deal_type | enum(sale, rent) |
| deal_value | int |
Primary key: deal_id.
deal_value is the sale price for a sale and the monthly rent for a rent deal, in rupees.
Examples
Example 1
Deal
| deal_id | broker | deal_type | deal_value |
|---|---|---|---|
| 1 | Sunil Realty | sale | 8500000 |
| 2 | Sunil Realty | rent | 32000 |
| 3 | Prime Homes | sale | 10000000 |
| 4 | Prime Homes | sale | 9900000 |
| 5 | Urban Keys | rent | 75000 |
| 6 | Urban Keys | sale | 21500000 |
Output
| deal_id | broker | commission |
|---|---|---|
| 1 | Sunil Realty | 170000 |
| 2 | Sunil Realty | 32000 |
| 3 | Prime Homes | 150000 |
| 4 | Prime Homes | 198000 |
| 5 | Urban Keys | 75000 |
| 6 | Urban Keys | 322500 |
How to solve Broker Commission by Deal Slab
The commission is a function of two columns, so it is one CASE expression evaluated per row. Order the branches so that the deal type is decided first: a rent deal pays its deal_value whatever the amount, and only for sales does the price slab matter. Because CASE stops at the first true branch, the second branch (deal_value < 10000000) is only ever reached by a sale, and the ELSE catches sales of ₹1 crore and above.
The boundary is the usual trap: deal 3 is exactly ₹1 crore, which is not below a crore, so it is paid at 1.5% (₹1,50,000), while deal 4 at ₹99 lakh earns 2% (₹1,98,000) — more commission on a cheaper flat, which is why such slabs are argued about.
Nested IFs say the same thing, and a UNION ALL of the rent rows and the sale rows splits the cases into two queries. One pass over the table.
Reference solution (MySQL)
SELECT deal_id, broker,
CASE
WHEN deal_type = 'rent' THEN deal_value
WHEN deal_value < 10000000 THEN deal_value * 0.02
ELSE deal_value * 0.015
END AS commission
FROM DealAnother way
SELECT deal_id, broker, IF(deal_type = 'rent', deal_value, IF(deal_value >= 10000000, deal_value * 15 / 1000, deal_value * 2 / 100)) AS commission FROM DealAnother way
SELECT deal_id, broker, deal_value AS commission FROM Deal WHERE deal_type = 'rent' UNION ALL SELECT deal_id, broker, deal_value * CASE WHEN deal_value >= 10000000 THEN 0.015 ELSE 0.02 END FROM Deal WHERE deal_type = 'sale'← Maintenance Tickets Breaching the Seven-Day SLA · Society Maintenance Dues Collected per Wing →