Cycle Count Shrinkage Value by Store and Month — SQL Problem
During a cycle count a store's staff count a shelf and compare it with the system quantity.
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #385
Problem statement
During a cycle count a store's staff count a shelf and compare it with the system quantity. When fewer units are found than the system expects, the gap is shrinkage (theft, damage, admin error) and is valued at unit_cost. Counts that find more units than the system, or exactly the same, are not shrinkage and add nothing.
For every store and calendar month with at least one short count, return store_id, count_month (as YYYY-MM), short_counts (the number of counts that found fewer units) and shrinkage_value (the total of (system_qty - counted_qty) × unit_cost over those counts, rounded to 2 decimal places). Order the rows by store_id, then by count_month.
Tables
Table: CycleCount
| Column | Type |
|---|---|
| count_id | int |
| store_id | int |
| sku | varchar |
| count_date | date |
| system_qty | int |
| counted_qty | int |
| unit_cost | decimal |
Primary key: count_id.
One row per shelf counted; unit_cost is the landed cost per unit in rupees.
Examples
Example 1
CycleCount
| count_id | store_id | sku | count_date | system_qty | counted_qty | unit_cost |
|---|---|---|---|---|---|---|
| 1 | 1 | GRO-1001 | 2025-01-08 | 40 | 37 | 512.5 |
| 2 | 1 | SNK-6001 | 2025-01-22 | 120 | 120 | 21.4 |
| 3 | 1 | PER-4001 | 2025-01-29 | 30 | 32 | 88 |
| 4 | 1 | DAI-2002 | 2025-02-03 | 25 | 21 | 68.25 |
| 5 | 2 | HOM-5001 | 2025-01-15 | 18 | 16 | 72 |
| 6 | 2 | BEV-3002 | 2025-01-31 | 12 | 9 | 240 |
| 7 | 2 | GRO-1003 | 2025-02-27 | 50 | 55 | 350 |
Output
| store_id | count_month | short_counts | shrinkage_value |
|---|---|---|---|
| 1 | 2025-01 | 1 | 1537.5 |
| 1 | 2025-02 | 1 | 273 |
| 2 | 2025-01 | 2 | 864 |
How to solve Cycle Count Shrinkage Value by Store and Month
Two decisions shape the query. First, only short counts are shrinkage: if overs were included, a shelf with three extra units would cancel three missing units elsewhere and hide a real loss. Filtering counted_qty < system_qty in WHERE keeps only the shortages (equal counts drop out because the comparison is strict).
Second, the month bucket. DATE_FORMAT(count_date, '%Y-%m') turns every date into its month label, and grouping by store and that label gives one row per store per month; a month with no shortage has no rows left after the filter and is therefore absent, as the statement asks. The label sorts chronologically because the year comes first and the month is zero-padded.
The value is (system_qty - counted_qty) × unit_cost summed and rounded to two places. The alternatives zero out non-short rows with CASE and drop shortage-free months in HAVING, or build the label from YEAR and MONTH with LPAD. All are one pass over the counts.
Reference solution (MySQL)
SELECT store_id,
DATE_FORMAT(count_date, '%Y-%m') AS count_month,
COUNT(*) AS short_counts,
ROUND(SUM((system_qty - counted_qty) * unit_cost), 2) AS shrinkage_value
FROM CycleCount
WHERE counted_qty < system_qty
GROUP BY store_id, DATE_FORMAT(count_date, '%Y-%m')
ORDER BY store_id, count_monthAnother way
SELECT store_id, count_month, SUM(is_short) AS short_counts, ROUND(SUM(loss), 2) AS shrinkage_value FROM (
SELECT store_id, LEFT(count_date, 7) AS count_month,
CASE WHEN counted_qty < system_qty THEN 1 ELSE 0 END AS is_short,
CASE WHEN counted_qty < system_qty THEN (system_qty - counted_qty) * unit_cost ELSE 0 END AS loss
FROM CycleCount) t
GROUP BY store_id, count_month
HAVING SUM(is_short) > 0
ORDER BY store_id, count_monthAnother way
SELECT store_id, count_month, COUNT(*) AS short_counts, ROUND(SUM(gap * unit_cost), 2) AS shrinkage_value FROM (SELECT store_id, CONCAT(YEAR(count_date), '-', LPAD(MONTH(count_date), 2, '0')) AS count_month, system_qty - counted_qty AS gap, unit_cost FROM CycleCount) c WHERE gap > 0 GROUP BY store_id, count_month ORDER BY store_id, count_month← Top-Selling SKU in Each Store, Ties Included · Store Revenue Against the Previous Calendar Month →