Drill 8 of 12 in E-commerce questions
0

Interview Question Bank · Sales velocity · LEFT JOIN · fixed date window

8. Days of inventory remaining

MediumE-commerceQuestion #92

Concept: Sales velocity · LEFT JOIN · fixed date windowshow

Days of inventory = stock on hand / average daily units sold. The average must divide by every day in the window, not just the days with sales, so take the window's total units and divide by its length. Products with no recent sales have no velocity, so LEFT JOIN the sales onto the catalog and let their result be NULL instead of crashing on a divide by zero.

Example (different data):

WITH u AS (
  SELECT part_id, SUM(qty) AS used_14d
  FROM part_usage
  WHERE used_on BETWEEN '2025-05-18' AND '2025-05-31'
  GROUP BY part_id
)
SELECT p.part_no, ROUND(p.in_stock * 14.0 / u.used_14d, 1) AS days_cover
FROM parts p
LEFT JOIN u ON u.part_id = p.id;

Your task

Quillmere's buyer reorders stock every Monday and wants to know how many days each product will last at its recent sales pace.

Use the 28 days from 2025-03-04 to 2025-03-31 (both included) and completed orders only. For every product in shop_products, return product (name), on_hand, units_28d (units sold in that window, 0 if none) and days_remaining (on_hand divided by average daily units, i.e. on_hand * 28 / units_28d, rounded to 1 decimal; NULL if nothing sold). Sort by days_remaining (lowest first, NULLs last), then product.

Starting Postgres in your browser…

Checking your access…