SQL Views — The 12M Row JOIN That Crashed Your Dashboard

-- ─────────────────────────────────────────────────────────────
-- SETUP: Create the base tables we'll build our view on top of
-- ─────────────────────────────────────────────────────────────
CREATE TABLE customers (
    customer_id   INT          PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    email         VARCHAR(150) NOT NULL,
    country       VARCHAR(50)  NOT NULL
);

CREATE TABLE orders (
    order_id     INT           PRIMARY KEY,
    customer_id  INT           NOT NULL REFERENCES customers(customer_id),
    order_date   DATE          NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL
);

-- ─────────────────────────────────────────────────────────────
-- SEED DATA: Realistic rows so we can see real output
-- ─────────────────────────────────────────────────────────────
INSERT INTO customers VALUES
    (1, 'Amara Osei',    'amara@example.com',  'Ghana'),
    (2, 'Lena Fischer',  'lena@example.com',   'Germany'),
    (3, 'Carlos Ruiz',   'carlos@example.com', 'Mexico');

INSERT INTO orders VALUES
    (101, 1, '2024-01-15',  89.99),
    (102, 1, '2024-03-22', 214.50),
    (103, 2, '2024-02-10',  49.00),
    (104, 3, '2024-04-01', 320.00),
    (105, 3, '2024-04-18',  75.25);

-- ─────────────────────────────────────────────────────────────
-- THE VIEW: Stores the query definition, NOT the result data.
-- Every SELECT against this view re-runs the JOIN live.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.full_name,
    c.country,
    COUNT(o.order_id)       AS total_orders,      -- aggregates across all rows for this customer
    SUM(o.total_amount)     AS lifetime_spend,    -- total money spent, ever
    MAX(o.order_date)       AS last_order_date    -- most recent purchase date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id  -- LEFT JOIN keeps customers with zero orders
GROUP BY
    c.customer_id,
    c.full_name,
    c.country;

-- ─────────────────────────────────────────────────────────────
-- USING THE VIEW: Looks exactly like querying a table
-- ─────────────────────────────────────────────────────────────
SELECT
    full_name,
    total_orders,
    lifetime_spend
FROM customer_order_summary
WHERE lifetime_spend > 100.00
ORDER BY lifetime_spend DESC;

-- ─────────────────────────────────────────────────────────────
-- BASE TABLE: Contains sensitive columns alongside public ones
-- ─────────────────────────────────────────────────────────────
CREATE TABLE employees (
    employee_id    INT           PRIMARY KEY,
    full_name      VARCHAR(100)  NOT NULL,
    department     VARCHAR(80)   NOT NULL,
    region         VARCHAR(50)   NOT NULL,
    annual_salary  NUMERIC(12,2) NOT NULL,   -- SENSITIVE
    bank_account   VARCHAR(30)   NOT NULL,   -- SENSITIVE
    hire_date      DATE          NOT NULL
);

INSERT INTO employees VALUES
    (1, 'Yuki Tanaka',    'Engineering', 'APAC',   95000.00, 'GB29NWBK60161331926819', '2021-06-01'),
    (2, 'Sofia Delgado',  'HR',          'EMEA',   72000.00, 'DE89370400440532013000', '2020-03-15'),
    (3, 'Marcus Webb',    'Engineering', 'EMEA',   88000.00, 'FR7614508059405402982935', '2019-11-22'),
    (4, 'Priya Sharma',   'Sales',       'APAC',   67000.00, 'IN30267931234567890123', '2022-09-10');

-- ─────────────────────────────────────────────────────────────
-- VIEW 1: Operations team — sees name, department, region only.
-- Salary and bank account columns are simply not included.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW employee_directory AS
SELECT
    employee_id,
    full_name,
    department,
    region,
    hire_date
FROM employees;  -- no salary, no bank_account — they don't exist in this view

-- ─────────────────────────────────────────────────────────────
-- VIEW 2: Payroll team — salary visible, but still no bank
-- account number (that's only for the finance system to access)
-- ─────────────────────────────────────────────────────────────
CREATE VIEW employee_payroll_summary AS
SELECT
    employee_id,
    full_name,
    department,
    annual_salary
FROM employees;  -- bank_account intentionally excluded

-- ─────────────────────────────────────────────────────────────
-- VIEW 3: EMEA regional manager — row-level restriction.
-- This view physically cannot return rows from other regions.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW emea_employee_directory AS
SELECT
    employee_id,
    full_name,
    department,
    hire_date
FROM employees
WHERE region = 'EMEA';  -- the filter lives in the database, not the application

-- ─────────────────────────────────────────────────────────────
-- GRANT access to the VIEW only. The ops_user role never gets
-- SELECT on the raw employees table.
-- ─────────────────────────────────────────────────────────────
-- GRANT SELECT ON employee_directory TO ops_user;
-- REVOKE SELECT ON employees FROM ops_user;

-- Test the EMEA view — only EMEA rows should appear
SELECT * FROM emea_employee_directory ORDER BY full_name;

-- ─────────────────────────────────────────────────────────────
-- SETUP
-- ─────────────────────────────────────────────────────────────
CREATE TABLE products (
    product_id   INT           PRIMARY KEY,
    product_name VARCHAR(100)  NOT NULL,
    category     VARCHAR(50)   NOT NULL,
    unit_price   NUMERIC(8,2)  NOT NULL,
    is_active    BOOLEAN       NOT NULL DEFAULT TRUE
);

INSERT INTO products VALUES
    (1, 'Wireless Keyboard', 'Electronics', 49.99, TRUE),
    (2, 'Desk Lamp',         'Office',      29.99, TRUE),
    (3, 'Ergonomic Chair',   'Furniture',  349.00, FALSE),
    (4, 'USB-C Hub',         'Electronics', 34.99, TRUE);

-- ─────────────────────────────────────────────────────────────
-- UPDATABLE VIEW: Single table, no aggregates, no DISTINCT.
-- The database can map each view row to exactly one table row.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW active_products AS
SELECT
    product_id,
    product_name,
    category,
    unit_price
FROM products
WHERE is_active = TRUE;  -- simple filter, view is still updatable

-- This UPDATE works — the database knows exactly which row to change
UPDATE active_products
SET unit_price = 54.99
WHERE product_id = 1;  -- maps cleanly to products row with product_id = 1

-- Confirm the change made it through to the base table
SELECT product_id, product_name, unit_price FROM products WHERE product_id = 1;

-- ─────────────────────────────────────────────────────────────
-- READ-ONLY VIEW: Uses GROUP BY + COUNT — not updatable.
-- The database can't reverse-engineer which base rows to touch.
-- ─────────────────────────────────────────────────────────────
CREATE VIEW product_count_by_category AS
SELECT
    category,
    COUNT(*)          AS product_count,
    AVG(unit_price)   AS avg_price
FROM products
GROUP BY category;

-- This would FAIL with: ERROR: cannot update a non-updatable view
-- UPDATE product_count_by_category SET avg_price = 40.00 WHERE category = 'Electronics';
-- (Commented out so the script runs cleanly — uncomment to see the error)

-- Safe to SELECT from it though
SELECT * FROM product_count_by_category ORDER BY category;

-- ─────────────────────────────────────────────────────────────
-- ORIGINAL VIEW (from our first section)
-- ─────────────────────────────────────────────────────────────
-- CREATE VIEW customer_order_summary AS ... (already created above)

-- ─────────────────────────────────────────────────────────────
-- SCENARIO: Business now wants average order value added.
-- Use CREATE OR REPLACE — keeps existing GRANTs and dependencies.
-- ─────────────────────────────────────────────────────────────
CREATE OR REPLACE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.full_name,
    c.country,
    COUNT(o.order_id)                       AS total_orders,
    SUM(o.total_amount)                     AS lifetime_spend,
    ROUND(AVG(o.total_amount), 2)           AS avg_order_value,  -- NEW column added safely
    MAX(o.order_date)                       AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.full_name,
    c.country;

-- ─────────────────────────────────────────────────────────────
-- CHECK DEPENDENCIES before dropping (PostgreSQL syntax).
-- Run this BEFORE any DROP VIEW to see what you'd break.
-- ─────────────────────────────────────────────────────────────
SELECT
    dependent_view.relname   AS view_that_depends,
    source_view.relname      AS depends_on_this_view
FROM pg_depend dep
JOIN pg_rewrite rw       ON dep.objid      = rw.oid
JOIN pg_class dependent_view ON rw.ev_class = dependent_view.oid
JOIN pg_class source_view    ON dep.refobjid = source_view.oid
WHERE source_view.relname = 'customer_order_summary'  -- replace with your view name
  AND dependent_view.relname != source_view.relname;  -- exclude self-reference

-- ─────────────────────────────────────────────────────────────
-- SAFE DROP: No cascade — fails loudly if dependencies exist.
-- That loud failure is a feature, not a bug.
-- ─────────────────────────────────────────────────────────────
-- DROP VIEW customer_order_summary;                 -- safe: errors if depended on
-- DROP VIEW customer_order_summary CASCADE;         -- dangerous: silently drops dependents

-- Verify the updated view includes the new column
SELECT
    full_name,
    total_orders,
    lifetime_spend,
    avg_order_value
FROM customer_order_summary
ORDER BY lifetime_spend DESC;