pg_reactive Example 8 -- date_trunc + GROUP BY

Order Analytics

Hourly order volume updates in real-time

Disconnected

Time Buckets

0

Total Orders

0

Total Revenue

$0.00

Avg Order

$0.00

Revenue by Hour

Waiting for data...

This query uses date_trunc('hour', created_at) to bucket orders by time window. The GROUP BY expression is recomputed on every INSERT, giving a live time-series without any external analytics pipeline.

-- Connect to the database
-- docker compose exec -T db psql -U postgres -d pg_reactive_test

-- Place a single order (current hour bucket grows)
INSERT INTO orders (customer_id, product_id, qty, total)
  VALUES (1, 2, 3, 149.97);

-- Batch of orders across different hours
INSERT INTO orders (customer_id, product_id, qty, total, created_at)
  VALUES
    (2, 1, 1, 79.99, NOW() - interval '1 hour'),
    (3, 3, 2, 699.98, NOW() - interval '2 hours'),
    (1, 2, 1, 49.99, NOW() - interval '3 hours');

-- High-value order (avg order value increases)
INSERT INTO orders (customer_id, product_id, qty, total)
  VALUES (2, 3, 10, 3499.90);

-- Delete an order (bucket shrinks or disappears)
DELETE FROM orders
  WHERE id = (SELECT MAX(id) FROM orders);