-- Materialized view: top customers with purchase history
CREATE MATERIALIZED VIEW customer_summary AS
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(o.total_agorot) AS lifetime_value_agorot,
  MAX(o.ordered_at) AS last_order_date,
  ARRAY_AGG(DISTINCT p.category) AS purchased_categories
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'delivered'
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
GROUP BY u.id, u.name, u.email
WITH DATA;

-- Create index on materialized view
CREATE INDEX idx_customer_summary_value
ON customer_summary(lifetime_value_agorot DESC);

-- Refresh (run nightly)
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_summary;
-- CONCURRENTLY allows reads during refresh

Database Normalization — 1NF, 2NF, 3NF

Database Normalization — 1NF, 2NF, 3NF

Database Normalization — 1NF, 2NF, 3NF