pg_reactive Example 9 -- Window Functions

Price Comparison

Rankings and percentiles shift in real-time
disconnected
Rank Product Category Price Quartile vs Average % of Max
Waiting for data...
This query computes four window functions simultaneously: RANK(), NTILE(4), ratio to AVG(), and percentage of MAX(). All rankings update atomically on every price change -- no client-side computation needed. 
-- Connect to the database
-- docker compose exec -T db psql -U postgres -d pg_reactive_test

-- Add a premium product (shifts all rankings)
INSERT INTO products (name, price, category)
  VALUES ('Gaming Laptop', 1299.99, 'electronics');

-- Add a budget product (changes quartile boundaries)
INSERT INTO products (name, price, category)
  VALUES ('USB Cable', 4.99, 'electronics');

-- Price update shifts rank, quartile, vs_avg, pct_of_max
UPDATE products SET price = 899.99
  WHERE name = 'Headphones';

-- Delete changes every metric for remaining products
DELETE FROM products
  WHERE name = 'USB Cable';