For each warehouse and SKU, choose the latest stock balance recorded on or before the reporting cutoff. Add those balances across warehouses and report how many locations supplied a usable balance. Adding several dates for one warehouse counts some of the same stock repeatedly.
What should a Sunday stock report say?
Imagine one SKU sold in Boston, Leeds and Lyon. Boston reports 12 units on Friday and seven on Sunday. Leeds reports zero on Saturday. Lyon’s first count arrives on Tuesday. The warehouse roster confirms that all three sites carried the SKU on Sunday. Assume a balance stays in effect until the next update and the ledger records every stock change. Each site has at most one closing balance per reporting date.
On Sunday, Boston contributes seven and Leeds contributes zero. Lyon’s Sunday balance is unknown. You can report seven known units across two of three warehouses. Reporting seven as the company total would hide a missing location. At an assumed unit cost of USD 20, the covered inventory is worth USD 140. The company-wide valuation still needs Lyon’s balance.
Run the cutoff before ranking
WITH roster(site) AS (VALUES ('Boston'),('Leeds'),('Lyon')),
stock(site,day,units) AS (
VALUES ('Boston','2026-11-06',12),
('Boston','2026-11-08',7),
('Leeds','2026-11-07',0),
('Lyon','2026-11-10',9)
), ranked AS (
SELECT site,day,units,ROW_NUMBER() OVER (
PARTITION BY site ORDER BY day DESC
) AS position
FROM stock WHERE day <= '2026-11-08'
)
SELECT r.site,s.day,s.units FROM roster r
LEFT JOIN ranked s ON s.site=r.site AND s.position=1
ORDER BY r.site;
The output gives Boston, November 8, 7; Leeds, November 7, 0; and Lyon, NULL, NULL. To check coverage, count the nonmissing unit balances and divide by the number of roster rows. Here that gives 2/3. If every location is missing, leave the total unknown. A reported zero counts toward coverage.
-- For the summary, replace the final SELECT above with:
SELECT SUM(s.units) AS known_units,
COUNT(s.units) AS sites_reporting,
COUNT(*) AS sites_expected,
CASE WHEN COUNT(s.units) = COUNT(*)
THEN SUM(s.units) END AS fleet_units
FROM roster r
LEFT JOIN ranked s ON s.site=r.site AND s.position=1;Why can a plausible total still be wrong?
Summing every eligible row produces 19 units because it counts both Boston balances. Using the largest recorded balance per site gives Boston 12 even though its stock fell. Taking a single latest reporting date across all sites drops Leeds. Each error changes which question the query answers. Keep the selected source date beside each quantity so a reviewer can trace the result.
When can you carry a balance forward?
An occasional physical count can go stale between observations. If receipts or sales can happen without a balance update, label the result as last observed and apply the inventory team’s freshness rule. With several SKUs, maintain the expected site-and-SKU roster and partition on both keys. For multiple revisions on one day, order by a reliable revision sequence and decide whether the report should reflect corrections learned after the cutoff.
For the ranking syntax, see the SQLite window function reference. Practice selecting among tied records in the latest-row SQL exercise, and compare missing and zero amounts in the missing-value exercise.