Streaming for Retail: Inventory, Pricing, and Demand Forecasting

Retail operations require real-time visibility into inventory levels across stores and warehouses, dynamic pricing based on demand signals, and demand forecasting from current sales velocity. Streaming SQL processes POS transactions, inventory events, and online orders continuously.

Retail Streaming Views

-- Real-time inventory across locations
CREATE MATERIALIZED VIEW inventory_realtime AS
SELECT sku, location_id, location_type,
  SUM(CASE WHEN event='restock' THEN qty WHEN event='sale' THEN -qty ELSE 0 END) as current_stock,
  COUNT(*) FILTER (WHERE event='sale' AND ts > NOW()-INTERVAL '1 hour') as sales_velocity_1h
FROM inventory_events GROUP BY sku, location_id, location_type;

-- Demand velocity for dynamic pricing
CREATE MATERIALIZED VIEW demand_signals AS
SELECT sku, category,
  COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '15 minutes') as demand_15min,
  COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '1 hour') as demand_1h,
  COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '24 hours') as demand_24h,
  CASE WHEN COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '15 minutes') >
    COUNT(*) FILTER (WHERE ts BETWEEN NOW()-INTERVAL '1 hour' AND NOW()-INTERVAL '45 minutes') * 4
    THEN 'SPIKE' ELSE 'NORMAL' END as demand_status
FROM sales_events GROUP BY sku, category;

Frequently Asked Questions

How does real-time inventory prevent overselling?

Streaming inventory views update with every sale event. When stock hits zero, the system immediately prevents further orders — no waiting for a nightly sync to discover overselling.

Can streaming SQL power dynamic pricing?

Yes. Streaming materialized views compute demand signals (sales velocity, view-to-purchase ratio) that feed pricing rules. Price adjustments based on real-time demand can increase revenue by 5-15%.