Stores Crossing ₹20,000 in Paid POS Bills

Easy
375.

Stores Crossing ₹20,000 in Paid POS Bills

EasyAggregation

MySQL

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

ColumnTypeValues
bill_idint
store_idint
bill_datedate
amountint
statusenumPAID, VOIDED, REFUNDED

Primary key: bill_id.

One row per bill rung up at a till; amount is the bill total in rupees.

Example

The rows must come back in the order shown.

PosBill

bill_idstore_idbill_dateamountstatus
112025-03-0112500PAID
212025-03-019000PAID
322025-03-0218000PAID
422025-03-027000VOIDED
532025-03-0320000PAID
642025-03-0326000REFUNDED
732025-03-041500REFUNDED

Output

store_idpaid_billsrevenue
1221500
3120000
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.

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

ColumnType
bill_idint
store_idint
bill_datedate
amountint
statusenum(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_idstore_idbill_dateamountstatus
112025-03-0112500PAID
212025-03-019000PAID
322025-03-0218000PAID
422025-03-027000VOIDED
532025-03-0320000PAID
642025-03-0326000REFUNDED
732025-03-041500REFUNDED

Output

store_idpaid_billsrevenue
1221500
3120000

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_id

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

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