Views

-- Create a view: customer order summary
CREATE VIEW customer_orders AS
SELECT
  u.id AS user_id,
  u.name AS customer_name,
  u.email,
  COUNT(o.id) AS total_orders,
  ROUND(SUM(o.total_amount), 2) AS lifetime_value,
  MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- Use view like a table
SELECT customer_name, lifetime_value FROM customer_orders
WHERE lifetime_value > 500
ORDER BY lifetime_value DESC;

-- Create view hiding sensitive payment data
CREATE VIEW order_summary AS
SELECT
  o.id, o.status, o.total_amount,
  u.name AS customer,
  CASE WHEN p.paid_at IS NOT NULL THEN 'Paid' ELSE 'Unpaid' END AS payment_status
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN payments p ON o.id = p.order_id;

-- Use the simplified view
SELECT * FROM order_summary WHERE status = 'pending';

-- Drop view
DROP VIEW customer_orders;

-- Replace existing view (PostgreSQL)
CREATE OR REPLACE VIEW customer_orders AS
SELECT u.name, COUNT(o.id) AS orders FROM users u LEFT JOIN orders o ON u.id=o.user_id GROUP BY u.id, u.name;