LogisticsX

Logistics / supply chain

LogisticsX runs a parcel and warehousing network of twelve warehouses across four Indian regions. Shipments travel between warehouses on one or two route legs, then a courier attempts delivery, sometimes more than once. Warehouses also hold stock for business customers. Analysts track promised-versus-actual delivery times, lane performance, hub dwell time, first-attempt success and days of stock cover, which are the core of logistics and supply-chain SQL interviews.

See the LogisticsX tables

LogisticsX schema

All tables live in the logisticsx schema, so write logisticsx.warehouses in your queries.

logisticsx.warehouses

Warehouses and hubs.

warehouse_id (INT) — PK
name (VARCHAR(60))
city (VARCHAR(40))
region (VARCHAR(10)) — 'North', 'West', 'South', 'East'
is_hub (BOOLEAN) — regional sorting hub
capacity_units (INT) — storage capacity in stock units

logisticsx.customers

Business customers who ship parcels and store stock.

customer_id (INT) — PK
company_name (VARCHAR(80))
industry (VARCHAR(20)) — 'ecommerce', 'pharma', 'electronics', 'fashion', 'fmcg'
tier (VARCHAR(10)) — 'standard' or 'priority'

logisticsx.couriers

Last-mile couriers.

courier_id (INT) — PK
full_name (VARCHAR(80))
vehicle_type (VARCHAR(10)) — 'bike', 'van', 'truck'
home_warehouse_id (INT) — FK → warehouses.warehouse_id

logisticsx.shipments

One row per parcel shipment.

shipment_id (INT) — PK
customer_id (INT) — FK → customers.customer_id
origin_warehouse_id (INT) — FK → warehouses.warehouse_id
destination_warehouse_id (INT) — FK → warehouses.warehouse_id; warehouse the last-mile courier delivers from
service_level (VARCHAR(10)) — 'standard' or 'express'
weight_kg (DECIMAL(7,2))
shipping_fee (DECIMAL(8,2)) — INR
created_at (TIMESTAMP)
promised_by (TIMESTAMP) — delivery deadline promised to the customer
status (VARCHAR(16)) — 'CREATED', 'IN_TRANSIT', 'OUT_FOR_DELIVERY', 'DELIVERED', 'RETURNED', 'CANCELLED'
delivered_at (TIMESTAMP) — nullable

logisticsx.routing

Line-haul legs between warehouses. Cross-region shipments go through the destination region's hub.

route_id (INT) — PK
shipment_id (INT) — FK → shipments.shipment_id
leg_number (INT) — 1, 2, …
from_warehouse_id (INT) — FK → warehouses.warehouse_id
to_warehouse_id (INT) — FK → warehouses.warehouse_id
planned_departure (TIMESTAMP)
planned_arrival (TIMESTAMP)
actual_departure (TIMESTAMP) — nullable
actual_arrival (TIMESTAMP) — nullable

logisticsx.deliveries

Last-mile delivery attempts.

delivery_id (INT) — PK
shipment_id (INT) — FK → shipments.shipment_id
courier_id (INT) — FK → couriers.courier_id
attempt_number (INT)
attempted_at (TIMESTAMP)
status (VARCHAR(10)) — 'SUCCESS', 'FAILED', 'RETURNED'
failure_reason (VARCHAR(30)) — nullable; 'customer_unavailable', 'address_issue', 'refused'

logisticsx.inventory

Current stock per warehouse and SKU.

inventory_id (INT) — PK
warehouse_id (INT) — FK → warehouses.warehouse_id
sku (VARCHAR(20))
quantity (INT) — units on hand
reorder_point (INT)
last_updated (TIMESTAMP)

logisticsx.inventory_movements

Stock in and out of warehouses (last 60 days).

movement_id (INT) — PK
warehouse_id (INT) — FK → warehouses.warehouse_id
sku (VARCHAR(20))
movement_type (VARCHAR(10)) — 'inbound' or 'outbound'
quantity (INT) — always positive
moved_at (TIMESTAMP)

Beginner (10)

BeginnerWarehouse capacity by regionOpen now
BeginnerThis week's shipments by statusOpen now
BeginnerExpress share of shipments (last 30 days)
BeginnerStock below the reorder point
BeginnerCourier fleet by vehicle type
BeginnerCancelled shipments by customer (last 30 days)
BeginnerHeaviest shipments in transit
BeginnerStock on hand per warehouse
BeginnerWhy delivery attempts fail
BeginnerPriority customers and their volume

Intermediate (10)

IntermediateOn-time delivery rate by service level
IntermediateSlowest lanes
IntermediateFirst-attempt success rate by courier
IntermediateShipments that have missed their promise
IntermediateDeparture delays by warehouse
IntermediateTop customers by monthly volume
IntermediateShipping revenue by origin region
IntermediateWarehouse fill rate
IntermediateHow many legs do shipments take?
IntermediateCourier failure rate (last 30 days)

Advanced (23)

AdvancedDoes a late first leg make delivery late?
AdvancedDwell time at transfer warehouses
AdvancedDays of stock cover
AdvancedSKUs at risk of stocking out
AdvancedCourier ranking within each warehouse
AdvancedWeekly SLA breach rate with a 4-week average
AdvancedData quality checks on route legs
AdvancedOutbound vs inbound balance by warehouse
AdvancedCustomer SLA report
AdvancedDeliveries that needed several attempts
AdvancedWarehouse reachability with a bounded recursive graph
AdvancedRunning inventory movement net with deterministic peers
AdvancedConditional inventory reservation without overselling
AdvancedWarehouse/SKU duplicates before an inventory upsert
AdvancedFULL OUTER coverage of stock and movement keys
AdvancedFind holes in shipment route-leg numbering
AdvancedLatest delivery-attempt state with timestamp ties
AdvancedSelect unstarted route legs with SKIP LOCKED
AdvancedWarehouses used as both origins and destinations
AdvancedBounded warehouse departure manifest as JSON
AdvancedShipment origin and destination foreign-key inspection
AdvancedService-level SLA comparisons stratified by destination region
AdvancedMERGE a stock delta and explain replay risk

Mock interview round (5)

AdvancedMock round 1: Yesterday's operations dashboard
AdvancedMock round 2: Worst lanes for late delivery
AdvancedMock round 3: Returns by customer industry
AdvancedMock round 4: Warehouse outbound throughput
AdvancedMock round 5: How much buffer do our promises have?