Dynamic Pricing with Real-Time Stream Processing

Dynamic pricing adjusts prices based on real-time demand, inventory levels, competitor pricing, and market conditions. Streaming SQL makes this accessible without data science infrastructure — demand signals, inventory status, and pricing rules are all SQL materialized views.

Dynamic Pricing Pipeline

-- Real-time demand signal
CREATE MATERIALIZED VIEW demand_signal AS
SELECT product_id,
  COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '15 minutes') as views_15min,
  COUNT(*) FILTER (WHERE action='add_to_cart' AND ts > NOW()-INTERVAL '1 hour') as carts_1h,
  COUNT(*) FILTER (WHERE action='purchase' AND ts > NOW()-INTERVAL '1 hour') as purchases_1h
FROM product_events GROUP BY product_id;

-- Price recommendation
CREATE MATERIALIZED VIEW price_recommendations AS
SELECT d.product_id, p.base_price, i.stock_level,
  d.views_15min, d.carts_1h,
  CASE
    WHEN i.stock_level < 10 AND d.views_15min > 100 THEN p.base_price * 1.20
    WHEN d.carts_1h > d.purchases_1h * 3 THEN p.base_price * 1.10
    WHEN d.views_15min < 5 AND i.stock_level > 1000 THEN p.base_price * 0.90
    ELSE p.base_price
  END as recommended_price
FROM demand_signal d
JOIN products p ON d.product_id = p.product_id
JOIN inventory i ON d.product_id = i.product_id;

Use Cases

• E-commerce: Adjust prices based on demand and inventory
• Ride-sharing: Surge pricing based on real-time demand/supply
• Hotels/Airlines: Revenue management based on booking velocity
• Energy: Real-time electricity pricing based on grid demand

Frequently Asked Questions

Is dynamic pricing ethical?

Dynamic pricing is standard practice in airlines, ride-sharing, and energy markets. Transparency is key — showing users why prices change builds trust. Streaming SQL makes pricing rules auditable and explainable.

How fast do prices need to update?

Depends on the industry. Ride-sharing: seconds. E-commerce: minutes. Hotels: hours. RisingWave supports all these frequencies through configurable materialized view refresh.