PostgreSQL
Mastery Guide

-- Create a new database
CREATE DATABASE vendorflow
  WITH
  OWNER     = postgres
  ENCODING  = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8';

-- Connect in psql
\c vendorflow

-- Create a schema (namespace for tables)
CREATE SCHEMA IF NOT EXISTS payments;

-- List all databases
\l

-- Show current database
SELECT current_database();

-- Drop a database (must be disconnected first)
DROP DATABASE IF EXISTS test_db;

-- Custom enum type
CREATE TYPE account_status AS ENUM ('active', 'suspended', 'closed');

-- Customers table — production-grade design
CREATE TABLE customers (
  id           UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  name         VARCHAR(100)   NOT NULL,
  email        TEXT           NOT NULL UNIQUE,
  phone        VARCHAR(20),
  status       account_status  NOT NULL DEFAULT 'active',
  metadata     JSONB          DEFAULT '{}',
  created_at   TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- Orders table with foreign key
CREATE TABLE orders (
  id           UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id  UUID           NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount > 0),
  currency     CHAR(3)        NOT NULL DEFAULT 'RWF',
  status       TEXT           NOT NULL DEFAULT 'pending',
  notes        TEXT,
  created_at   TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- Modify existing table
ALTER TABLE customers ADD COLUMN date_of_birth DATE;
ALTER TABLE customers DROP COLUMN date_of_birth;
ALTER TABLE customers RENAME COLUMN phone TO phone_number;

\l              -- list databases
\c dbname      -- connect to database
\dt            -- list tables in current schema
\dt schema.*  -- list tables in specific schema
\d tablename   -- describe table (columns, types, constraints)
\di            -- list indexes
\dv            -- list views
\du            -- list users and roles
\dn            -- list schemas
\timing        -- toggle query execution time display
\x             -- toggle expanded display (great for wide rows)
\i file.sql   -- execute SQL file
\q             -- quit psql

-- Search pg_catalog for objects
SELECT tablename, schemaname
FROM   pg_catalog.pg_tables
WHERE  schemaname NOT IN ('pg_catalog', 'information_schema');

CREATE TABLE products (
  id       UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
  name     VARCHAR(200)    NOT NULL,
  price    NUMERIC(10, 2)  NOT NULL,
  stock    INTEGER         NOT NULL DEFAULT 0
);

CREATE TABLE order_items (
  id         UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id   UUID           NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,
  product_id UUID           NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity   INTEGER        NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10, 2) NOT NULL,
  UNIQUE (order_id, product_id)  -- composite unique constraint
);

-- ── INNER JOIN: only matching rows ────────────────────────
SELECT
  c.name            AS customer,
  o.id              AS order_id,
  o.total_amount,
  o.status
FROM       customers   c
INNER JOIN orders     o ON o.customer_id = c.id
ORDER BY   o.created_at DESC;

-- ── LEFT JOIN: all customers, even those without orders ───
SELECT
  c.name,
  c.email,
  COUNT(o.id)       AS order_count,
  COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM      customers  c
LEFT JOIN orders    o ON o.customer_id = c.id
GROUP BY  c.id, c.name, c.email
ORDER BY  total_spent DESC;

-- ── FULL OUTER JOIN: all rows from both sides ────────────
SELECT
  c.name      AS customer_name,
  p.name      AS product_name,
  oi.quantity
FROM            customers   c
FULL OUTER JOIN orders      o  ON o.customer_id  = c.id
FULL OUTER JOIN order_items oi ON oi.order_id    = o.id
FULL OUTER JOIN products    p  ON p.id          = oi.product_id;

-- ── MULTI-TABLE JOIN: real order report ──────────────────
SELECT
  c.name                              AS customer,
  o.id                                AS order_ref,
  o.created_at::date                  AS order_date,
  p.name                              AS product,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price         AS line_total,
  o.total_amount
FROM       customers   c
INNER JOIN orders      o  ON o.customer_id  = c.id
INNER JOIN order_items oi ON oi.order_id    = o.id
INNER JOIN products    p  ON p.id          = oi.product_id
ORDER BY   o.created_at DESC, c.name;

-- Insert with JSONB metadata
INSERT INTO customers (name, email, metadata)
VALUES ('Alice', 'alice@test.rw', '{"tier": "gold", "country": "RW", "preferences": {"notifications": true}}');

-- Access JSONB fields
SELECT
  name,
  metadata ->> 'tier'                    AS tier,       -- text value
  metadata -> 'preferences'             AS prefs,      -- JSON value
  metadata -> 'preferences' ->> 'notifications' AS notifs
FROM customers
WHERE metadata @> '{"tier": "gold"}';         -- @> contains operator

-- Filter by JSONB value
SELECT * FROM customers
WHERE  metadata ->> 'country' = 'RW'
  AND  (metadata -> 'preferences' ->> 'notifications')::boolean = true;

-- JSONB key exists
SELECT * FROM customers WHERE metadata ? 'tier';

-- Update JSONB field
UPDATE customers
SET    metadata = metadata || '{"tier": "platinum"}'::jsonb
WHERE  id = '...';

-- Aggregate JSONB to array
SELECT jsonb_agg(jsonb_build_object('name', name, 'email', email))
FROM   customers
WHERE  status = 'active';

-- ── BASIC CTE: customer revenue summary ─────────────────
WITH customer_revenue AS (
  SELECT
    c.id,
    c.name,
    c.email,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.id)          AS order_count
  FROM       customers c
  INNER JOIN orders    o ON o.customer_id = c.id
  WHERE      o.status = 'completed'
  GROUP BY   c.id, c.name, c.email
)
SELECT
  name,
  email,
  order_count,
  total_spent,
  CASE
    WHEN total_spent >= 500000 THEN 'Platinum'
    WHEN total_spent >= 100000 THEN 'Gold'
    ELSE                            'Standard'
  END AS tier
FROM  customer_revenue
ORDER BY total_spent DESC;

-- ── CHAINED CTEs ────────────────────────────────────────
WITH
  monthly_revenue AS (
    SELECT
      DATE_TRUNC('month', created_at) AS month,
      SUM(total_amount)               AS revenue
    FROM   orders
    WHERE  status != 'cancelled'
    GROUP BY 1
  ),
  growth AS (
    SELECT
      month,
      revenue,
      LAG(revenue) OVER (ORDER BY month) AS prev_month,
      revenue - LAG(revenue) OVER (ORDER BY month) AS change
    FROM monthly_revenue
  )
SELECT
  month,
  revenue,
  change,
  ROUND(change::numeric / prev_month * 100, 2) AS growth_pct
FROM  growth
WHERE prev_month IS NOT NULL
ORDER BY month;

-- Rank customers by spend (no gaps: DENSE_RANK)
SELECT
  c.name,
  SUM(o.total_amount)  AS total_spent,
  RANK()       OVER (ORDER BY SUM(o.total_amount) DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS dense_rank,
  NTILE(4)     OVER (ORDER BY SUM(o.total_amount) DESC) AS quartile
FROM       customers c
INNER JOIN orders    o ON o.customer_id = c.id
GROUP BY   c.id, c.name;

-- Running total (cumulative sum)
SELECT
  created_at::date       AS date,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                      AS running_total
FROM   orders
WHERE  status = 'completed'
ORDER BY created_at;

-- Month-over-month comparison with LAG
SELECT
  DATE_TRUNC('month', created_at)         AS month,
  SUM(total_amount)                        AS revenue,
  LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', created_at))
                                           AS prev_month_revenue
FROM     orders
WHERE    status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Row number within partition (top 3 orders per customer)
WITH ranked AS (
  SELECT
    customer_id,
    total_amount,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY total_amount DESC
    ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

-- Regular view: stored query, always fresh
CREATE OR REPLACE VIEW v_active_customers AS
SELECT
  id, name, email, created_at,
  metadata ->> 'tier' AS tier
FROM  customers
WHERE status = 'active'
  AND deleted_at IS NULL;

-- Query the view like a table
SELECT * FROM v_active_customers WHERE tier = 'gold';

-- Materialized view: cached result, manually refreshed
CREATE MATERIALIZED VIEW mv_customer_stats AS
SELECT
  c.id,
  c.name,
  COUNT(o.id)          AS order_count,
  SUM(o.total_amount)  AS total_spent,
  MAX(o.created_at)    AS last_order
FROM      customers c
LEFT JOIN orders    o ON o.customer_id = c.id
GROUP BY  c.id, c.name
WITH DATA;  -- populate immediately

-- Create index on materialized view
CREATE INDEX ON mv_customer_stats (total_spent DESC);

-- Refresh materialized view (schedule with pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_stats;

-- VendorFlow: multi-vendor payment platform schema

CREATE TYPE user_role       AS ENUM ('customer', 'vendor', 'admin');
CREATE TYPE payment_status  AS ENUM ('pending', 'processing', 'success', 'failed', 'refunded');
CREATE TYPE tx_type         AS ENUM ('debit', 'credit', 'fee', 'refund');

CREATE TABLE users (
  id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  email        TEXT        NOT NULL UNIQUE,
  phone        VARCHAR(20) UNIQUE,
  role         user_role   NOT NULL DEFAULT 'customer',
  kyc_status   TEXT        NOT NULL DEFAULT 'pending',
  metadata     JSONB       NOT NULL DEFAULT '{}',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE wallets (
  id          UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID           NOT NULL UNIQUE REFERENCES users(id),
  balance     NUMERIC(15, 2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  currency    CHAR(3)        NOT NULL DEFAULT 'RWF',
  is_frozen   BOOLEAN        NOT NULL DEFAULT false,
  updated_at  TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

CREATE TABLE payments (
  id           UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  reference    TEXT           NOT NULL UNIQUE,
  sender_id    UUID           NOT NULL REFERENCES users(id),
  recipient_id UUID           NOT NULL REFERENCES users(id),
  amount       NUMERIC(15, 2) NOT NULL CHECK (amount > 0),
  fee          NUMERIC(10, 2) NOT NULL DEFAULT 0,
  currency     CHAR(3)        NOT NULL DEFAULT 'RWF',
  status       payment_status  NOT NULL DEFAULT 'pending',
  provider     TEXT           NOT NULL,
  metadata     JSONB          NOT NULL DEFAULT '{}',
  created_at   TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  CONSTRAINT   chk_different_parties CHECK (sender_id != recipient_id)
);

CREATE TABLE ledger (
  id          BIGSERIAL      PRIMARY KEY,
  payment_id  UUID           NOT NULL REFERENCES payments(id),
  wallet_id   UUID           NOT NULL REFERENCES wallets(id),
  type        tx_type        NOT NULL,
  amount      NUMERIC(15, 2) NOT NULL,
  balance_before NUMERIC(15, 2) NOT NULL,
  balance_after  NUMERIC(15, 2) NOT NULL,
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- Performance indexes
CREATE INDEX idx_payments_sender     ON payments (sender_id,    created_at DESC);
CREATE INDEX idx_payments_recipient  ON payments (recipient_id, created_at DESC);
CREATE INDEX idx_payments_status     ON payments (status)  WHERE status = 'pending';
CREATE INDEX idx_ledger_wallet       ON ledger   (wallet_id, created_at DESC);
CREATE INDEX idx_payments_metadata   ON payments USING GIN (metadata);

-- Fraud detection query: high-velocity transactions
SELECT
  sender_id,
  COUNT(*)                                  AS tx_count,
  SUM(amount)                               AS total_sent,
  MAX(amount)                               AS largest_tx
FROM   payments
WHERE  created_at >= NOW() - INTERVAL '1 hour'
  AND  status != 'failed'
GROUP BY sender_id
HAVING
  COUNT(*) > 10           -- more than 10 transactions per hour
  OR SUM(amount) > 5000000 -- or over 5M RWF sent
ORDER BY total_sent DESC;