ตั้งชื่อให้ query ที่ซับซ้อน แล้วทำให้ข้อมูลใช้งานและเชื่อถือได้ง่ายขึ้น

CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS analytics;

CREATE TABLE raw.customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  email VARCHAR(150),
  segment VARCHAR(30) NOT NULL
);

CREATE TABLE raw.orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES raw.customers(customer_id),
  ordered_at TIMESTAMP NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(12,2) NOT NULL
);

INSERT INTO raw.customers VALUES
 (1,'Aster Studio','[email protected]','SME'),
 (2,'Blue River','[email protected]','Enterprise'),
 (3,'Cedar Lab','[email protected]','Education');

INSERT INTO raw.orders VALUES
 (101,1,'2026-08-01 09:15','completed',12500.00),
 (102,1,'2026-08-03 13:40','cancelled',3200.00),
 (103,2,'2026-08-04 10:10','completed',54000.00),
 (104,3,'2026-08-05 16:25','pending',7800.00),
 (105,2,'2026-08-07 11:30','completed',18500.00);

CREATE VIEW analytics.v_completed_orders AS
SELECT
  o.order_id,
  o.ordered_at,
  CAST(o.ordered_at AS DATE) AS order_date,
  c.customer_id,
  c.customer_name,
  c.segment,
  o.amount
FROM raw.orders AS o
JOIN raw.customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

SELECT *
FROM analytics.v_completed_orders
ORDER BY ordered_at;

CREATE VIEW analytics.v_daily_revenue AS
SELECT
  order_date,
  COUNT(*) AS completed_orders,
  COUNT(DISTINCT customer_id) AS active_customers,
  SUM(amount) AS revenue,
  AVG(amount) AS average_order_value
FROM analytics.v_completed_orders
GROUP BY order_date;

SELECT *
FROM analytics.v_daily_revenue
ORDER BY order_date;

CREATE OR REPLACE VIEW analytics.v_daily_revenue AS
SELECT
  order_date,
  COUNT(*) AS completed_orders,
  COUNT(DISTINCT customer_id) AS active_customers,
  SUM(amount) AS revenue,
  AVG(amount) AS average_order_value,
  MAX(amount) AS largest_order
FROM analytics.v_completed_orders
GROUP BY order_date;

-- Example role names; adapt to your environment.
REVOKE ALL ON raw.customers, raw.orders FROM analyst_role;
GRANT USAGE ON SCHEMA analytics TO analyst_role;
GRANT SELECT ON analytics.v_completed_orders,
                analytics.v_daily_revenue
TO analyst_role;