Live Analytics Dashboard

-- Revenue by hour (auto-updating)
SELECT 
  DATE_TRUNC('hour', created_at) AS hour,
  SUM(total) AS revenue,
  COUNT(*) AS orders,
  AVG(total) AS avg_order_value
FROM orders
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour DESC;

-- Top products (live leaderboard)
SELECT 
  p.name,
  p.image_url,
  SUM(oi.quantity) AS units_sold,
  SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
ORDER BY units_sold DESC
LIMIT 10;

// Subscribe to hourly revenue chart
const revenueSub = await client.subscribe(
  `SELECT DATE_TRUNC('hour', created_at) AS hour,
          SUM(total) AS revenue, COUNT(*) AS orders
   FROM orders WHERE created_at > NOW() - INTERVAL '24 hours'
   GROUP BY hour ORDER BY hour DESC`,
  (delta) => {
    // Update chart with new data
    delta.inserts.forEach(row => chart.addDataPoint(row));
    delta.updates.forEach(row => chart.updateDataPoint(row.hour, row));
  }
);

// Live updating KPI cards
const kpiSub = await client.subscribe(
  `SELECT 
    COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '1 hour') as orders_last_hour,
    SUM(total) FILTER (WHERE status = 'pending') as pending_revenue,
    AVG(total) as avg_order_value
   FROM orders`,
  (delta) => {
    updateKPIs(delta.inserts[0]);
  }
);