Slab Tariff Energy Charges

Medium
506.

Slab Tariff Energy Charges

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

ColumnTypeValues
consumer_noint
bill_monthdate
categoryenumdomestic, commercial
unitsint

Primary key (together): consumer_no, bill_month.

Units (kWh) consumed by one connection in one month; bill_month is the first day of that month.

Example

The rows must come back in the order shown.

Consumption

consumer_nobill_monthcategoryunits
500112024-06-01domestic85
500112024-07-01domestic100
500272024-06-01domestic160
500272024-07-01domestic200
500342024-06-01domestic275
500342024-07-01domestic0
610022024-06-01commercial150

Output

consumer_nobill_monthenergy_charge
500112024-06-01255
500112024-07-01300
500272024-06-01600
500272024-07-01800
500342024-06-011400
500342024-07-010
610022024-06-011350
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.

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

ColumnType
consumer_noint
bill_monthdate
categoryenum(domestic, commercial)
unitsint

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_nobill_monthcategoryunits
500112024-06-01domestic85
500112024-07-01domestic100
500272024-06-01domestic160
500272024-07-01domestic200
500342024-06-01domestic275
500342024-07-01domestic0
610022024-06-01commercial150

Output

consumer_nobill_monthenergy_charge
500112024-06-01255
500112024-07-01300
500272024-06-01600
500272024-07-01800
500342024-06-011400
500342024-07-010
610022024-06-011350

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_month

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