Cycle Count Shrinkage Value by Store and Month

Medium
385.

Cycle Count Shrinkage Value by Store and Month

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

ColumnType
count_idint
store_idint
skuvarchar
count_datedate
system_qtyint
counted_qtyint
unit_costdecimal

Primary key: count_id.

One row per shelf counted; unit_cost is the landed cost per unit in rupees.

Example

The rows must come back in the order shown.

CycleCount

count_idstore_idskucount_datesystem_qtycounted_qtyunit_cost
11GRO-10012025-01-084037512.5
21SNK-60012025-01-2212012021.4
31PER-40012025-01-29303288
41DAI-20022025-02-03252168.25
52HOM-50012025-01-15181672
62BEV-30022025-01-31129240
72GRO-10032025-02-275055350

Output

store_idcount_monthshort_countsshrinkage_value
12025-0111537.5
12025-021273
22025-012864
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.

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

ColumnType
count_idint
store_idint
skuvarchar
count_datedate
system_qtyint
counted_qtyint
unit_costdecimal

Primary key: count_id.

One row per shelf counted; unit_cost is the landed cost per unit in rupees.

Examples

Example 1

CycleCount

count_idstore_idskucount_datesystem_qtycounted_qtyunit_cost
11GRO-10012025-01-084037512.5
21SNK-60012025-01-2212012021.4
31PER-40012025-01-29303288
41DAI-20022025-02-03252168.25
52HOM-50012025-01-15181672
62BEV-30022025-01-31129240
72GRO-10032025-02-275055350

Output

store_idcount_monthshort_countsshrinkage_value
12025-0111537.5
12025-021273
22025-012864

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_month

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

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