Broker Commission by Deal Slab

Easy
353.

Broker Commission by Deal Slab

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

ColumnTypeValues
deal_idint
brokervarchar
deal_typeenumsale, rent
deal_valueint

Primary key: deal_id.

deal_value is the sale price for a sale and the monthly rent for a rent deal, in rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Deal

deal_idbrokerdeal_typedeal_value
1Sunil Realtysale8500000
2Sunil Realtyrent32000
3Prime Homessale10000000
4Prime Homessale9900000
5Urban Keysrent75000
6Urban Keyssale21500000

Output

deal_idbrokercommission
1Sunil Realty170000
2Sunil Realty32000
3Prime Homes150000
4Prime Homes198000
5Urban Keys75000
6Urban Keys322500
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
deal_idint
brokervarchar
deal_typeenum(sale, rent)
deal_valueint

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_idbrokerdeal_typedeal_value
1Sunil Realtysale8500000
2Sunil Realtyrent32000
3Prime Homessale10000000
4Prime Homessale9900000
5Urban Keysrent75000
6Urban Keyssale21500000

Output

deal_idbrokercommission
1Sunil Realty170000
2Sunil Realty32000
3Prime Homes150000
4Prime Homes198000
5Urban Keys75000
6Urban Keys322500

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 Deal

Another 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 Deal

Another 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 →