Slab Tariff Energy Charges — SQL Conditional Logic Problem
The electricity board bills domestic consumers on a telescopic slab tariff: the first 100 units of a month cost ₹3 each, units 101 to 200 cost ₹5 each, and…
- Difficulty: Medium
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #506
Problem statement
The electricity board bills domestic consumers on a telescopic slab tariff: the first 100 units of a month cost ₹3 each, units 101 to 200 cost ₹5 each, and every unit above 200 costs ₹8. Commercial connections pay a flat ₹9 for every unit.
For every row of Consumption, return consumer_no, bill_month and energy_charge in rupees. A domestic consumer using 160 units pays 100 × 3 + 60 × 5 = ₹600. Order by consumer_no, then bill_month.
Tables
Table: Consumption
| Column | Type |
|---|---|
| consumer_no | int |
| bill_month | date |
| category | enum(domestic, commercial) |
| units | int |
Primary key: consumer_no, bill_month.
Units (kWh) consumed by one connection in one month; bill_month is the first day of that month.
Examples
Example 1
Consumption
| consumer_no | bill_month | category | units |
|---|---|---|---|
| 50011 | 2024-06-01 | domestic | 85 |
| 50011 | 2024-07-01 | domestic | 100 |
| 50027 | 2024-06-01 | domestic | 160 |
| 50027 | 2024-07-01 | domestic | 200 |
| 50034 | 2024-06-01 | domestic | 275 |
| 50034 | 2024-07-01 | domestic | 0 |
| 61002 | 2024-06-01 | commercial | 150 |
Output
| consumer_no | bill_month | energy_charge |
|---|---|---|
| 50011 | 2024-06-01 | 255 |
| 50011 | 2024-07-01 | 300 |
| 50027 | 2024-06-01 | 600 |
| 50027 | 2024-07-01 | 800 |
| 50034 | 2024-06-01 | 1400 |
| 50034 | 2024-07-01 | 0 |
| 61002 | 2024-06-01 | 1350 |
How to solve Slab Tariff Energy Charges
A telescopic tariff charges each slice of consumption at its own slab's rate. The CASE version picks the slab the total falls in and adds the fixed cost of all the full slabs below it: up to 100 units it is units * 3; up to 200 it is ₹300 for the first slab plus (units - 100) * 5; above 200 it is ₹300 + ₹500 = ₹800 plus (units - 200) * 8. Commercial rows are caught by the first branch and pay the flat rate. Because CASE stops at the first true branch, the later conditions need no lower bounds.
The boundary units matter: 100 units is ₹300 and 101 is ₹305, 200 is ₹800 and 201 is ₹808 — the hidden data tests these.
The alternative computes the units in each slab directly — LEAST(units, 100) in the first, LEAST(GREATEST(units - 100, 0), 100) in the second, GREATEST(units - 200, 0) in the third — and sums slab units times rates. It generalises nicely to many slabs. Both are a per-row calculation, one pass over the table.
Reference solution (MySQL)
SELECT consumer_no, bill_month,
CASE
WHEN category = 'commercial' THEN units * 9
WHEN units <= 100 THEN units * 3
WHEN units <= 200 THEN 300 + (units - 100) * 5
ELSE 800 + (units - 200) * 8
END AS energy_charge
FROM Consumption
ORDER BY consumer_no, bill_monthAnother way
SELECT consumer_no, bill_month,
IF(category = 'commercial', units * 9,
LEAST(units, 100) * 3
+ LEAST(GREATEST(units - 100, 0), 100) * 5
+ GREATEST(units - 200, 0) * 8) AS energy_charge
FROM Consumption
ORDER BY consumer_no, bill_month← Power Outages Lasting Four Hours or More · Units Consumed From Cumulative Meter Readings →