Stores Crossing ₹20,000 in Paid POS Bills — SQL Problem
The POS system records every bill rung up at a till; a bill is later VOIDED if the cashier cancelled it or REFUNDED if the customer returned the goods.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #375
Problem statement
The POS system records every bill rung up at a till; a bill is later VOIDED if the cashier cancelled it or REFUNDED if the customer returned the goods. Only PAID bills count as revenue.
Return store_id, paid_bills (the number of paid bills) and revenue (the sum of their amounts) for every store whose paid revenue is at least 20000 rupees. Order the rows by revenue highest first, then by store_id.
Tables
Table: PosBill
| Column | Type |
|---|---|
| bill_id | int |
| store_id | int |
| bill_date | date |
| amount | int |
| status | enum(PAID, VOIDED, REFUNDED) |
Primary key: bill_id.
One row per bill rung up at a till; amount is the bill total in rupees.
Examples
Example 1
PosBill
| bill_id | store_id | bill_date | amount | status |
|---|---|---|---|---|
| 1 | 1 | 2025-03-01 | 12500 | PAID |
| 2 | 1 | 2025-03-01 | 9000 | PAID |
| 3 | 2 | 2025-03-02 | 18000 | PAID |
| 4 | 2 | 2025-03-02 | 7000 | VOIDED |
| 5 | 3 | 2025-03-03 | 20000 | PAID |
| 6 | 4 | 2025-03-03 | 26000 | REFUNDED |
| 7 | 3 | 2025-03-04 | 1500 | REFUNDED |
Output
| store_id | paid_bills | revenue |
|---|---|---|
| 1 | 2 | 21500 |
| 3 | 1 | 20000 |
How to solve Stores Crossing ₹20,000 in Paid POS Bills
Two filters happen at two different moments. The status test is about individual bills, so it goes in WHERE and runs before grouping — voided and refunded bills never enter a store's count or sum. The revenue threshold is about a whole store, so it can only be tested after GROUP BY store_id has formed the groups, which is what HAVING is for.
A store whose bills are all voided or refunded has no paid rows, so it forms no group and is not in the answer, which is right: its revenue is 0. The threshold is inclusive, so a store at exactly ₹20,000 is listed.
The same answer comes from grouping in a derived table and filtering its revenue column outside, or from conditional sums over all bills with the test inside CASE. The first form is the cheapest: an index on (status, store_id) lets the database read only the paid bills.
Reference solution (MySQL)
SELECT store_id, COUNT(*) AS paid_bills, SUM(amount) AS revenue
FROM PosBill
WHERE status = 'PAID'
GROUP BY store_id
HAVING SUM(amount) >= 20000
ORDER BY revenue DESC, store_idAnother way
SELECT store_id, paid_bills, revenue FROM (SELECT store_id, COUNT(*) AS paid_bills, SUM(amount) AS revenue FROM PosBill WHERE status = 'PAID' GROUP BY store_id) t WHERE revenue >= 20000 ORDER BY revenue DESC, store_idAnother way
SELECT store_id, SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) AS paid_bills, SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END) AS revenue FROM PosBill GROUP BY store_id HAVING SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END) >= 20000 ORDER BY revenue DESC, store_id← Suppliers With No Purchase Order in 2024 · Category Codes From SKU Prefixes →