DB Exercises
0
← Exercises/
CTEs•12 of 12
PrevNext
CTEsAdvancedPRO

Warehouse Inventory Stockout Risk & Reorder Priority

Scenario

Warehouse fulfillment automated replenishment systems compare current available on-hand stock against 7-day average daily sales velocity to calculate estimated days of supply.

Task
Write a query using a CTE to evaluate inventory health for each product in `warehouse_stock`: 1. In a CTE `velocity`, calculate: - `sku` - `current_stock` - `daily_sales_velocity`: `daily_sales` - `days_of_supply`: `ROUND(current_stock / daily_sales, 1)` 2. In the outer query, filter for products where `days_of_supply <= 10.0` (urgent replenishment risk). Return: 1. `sku` 2. `product_name` 3. `current_stock` 4. `days_of_supply` Order results by `days_of_supply` ascending, then by `sku` ascending.
Expected Output Format

At-risk inventory items with 10 or fewer days of remaining stock.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Warehouse Inventory Stockout Risk & Reorder Priority” SQL Problem

Business Scenario

Warehouse fulfillment automated replenishment systems compare current available on-hand stock against 7-day average daily sales velocity to calculate estimated days of supply.

Expected Output & Criteria

At-risk inventory items with 10 or fewer days of remaining stock.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE warehouse_stock (
        sku VARCHAR(20) PRIMARY KEY,
        product_name VARCHAR(100) NOT NULL,
        current_stock INT NOT NULL,
        daily_sales NUMERIC(6, 2) NOT NULL
      );

Target SQL Clauses & Operators

WITHROUNDORDER BY

Want more CTEs practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View CTEs Track→