Section C · Query patterns

SQL Pattern Library

30+ named SQL patterns against the contract data model. Each with a rationale, a query, and the gotchas worth knowing.

How to read this

Patterns are grouped by entity. Each entry has:

  • What it answers — the business question.
  • The query — copy-paste ready (Postgres dialect; trivially portable).
  • Notes — gotchas, dialect variations, when to materialize as a mart.

These patterns assume the schema from chapter 01. Substitute table names as needed for your variant.

A · Agreements & renewals

A1. Renewals due in next N days

Answers: which agreements end soon enough to warrant action.

SELECT
  a.agreement_id, s.canonical_name AS supplier,
  a.current_term_end,
  a.current_term_end - CURRENT_DATE AS days_until_renewal
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE a.customer_id = 'cust_acme'
  AND a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE
                              AND CURRENT_DATE + INTERVAL '90 days'
ORDER BY days_until_renewal;

A2. Renewals with notice deadlines

Answers: when does the customer need to give notice to cancel.

WITH renewal_obligations AS (
  SELECT
    agreement_id,
    (value->>'notice_period_days')::INT AS notice_days,
    (value->>'auto_renew')::BOOLEAN AS auto_renew
  FROM obligations
  WHERE obligation_type = 'renewal_notice' AND status != 'superseded'
)
SELECT
  a.agreement_id, s.canonical_name,
  a.current_term_end,
  ro.notice_days,
  a.current_term_end - INTERVAL '1 day' * COALESCE(ro.notice_days, 60) AS notice_deadline,
  ro.auto_renew
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
LEFT JOIN renewal_obligations ro ON ro.agreement_id = a.agreement_id
WHERE a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '120 days'
ORDER BY notice_deadline;

A3. Auto-renew agreements about to renew silently

SELECT a.agreement_id, s.canonical_name, a.current_term_end
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
JOIN obligations o
  ON o.agreement_id = a.agreement_id
 AND o.obligation_type = 'renewal_notice'
 AND (o.value->>'auto_renew')::BOOLEAN = true
WHERE a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

A4. Latest amendment per agreement

SELECT a.agreement_id,
       MAX(d.ingested_at) AS latest_amendment_at
FROM agreements a
JOIN document_agreement_links dal ON dal.agreement_id = a.agreement_id
JOIN documents d ON d.document_id = dal.document_id
WHERE dal.role = 'amendment'
GROUP BY a.agreement_id;

A5. Agreements expiring without SOWs

SELECT a.agreement_id, s.canonical_name, a.current_term_end
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE a.current_term_end <= CURRENT_DATE + INTERVAL '60 days'
  AND a.status = 'active'
  AND NOT EXISTS (
    SELECT 1 FROM document_agreement_links dal
    WHERE dal.agreement_id = a.agreement_id AND dal.role = 'sow'
  );

B · Documents & provenance

B1. Latest extraction per document

SELECT DISTINCT ON (e.document_id)
  e.extraction_id, e.document_id, e.model_version, e.extracted_at
FROM extractions e
ORDER BY e.document_id, e.extracted_at DESC, e.model_version DESC;

B2. Orphaned amendments (no master MSA)

SELECT d.document_id, d.source_uri, d.ingested_at
FROM documents d
WHERE d.doc_class = 'amendment'
  AND NOT EXISTS (
    SELECT 1 FROM document_agreement_links dal
    WHERE dal.document_id = d.document_id
  );

B3. Documents not yet extracted with current model

SELECT d.document_id, d.doc_class, d.ingested_at
FROM documents d
LEFT JOIN extractions e
  ON e.document_id = d.document_id
 AND e.model_version = 'extract-v4.2.1'
WHERE e.extraction_id IS NULL
  AND d.customer_id = 'cust_acme'
ORDER BY d.ingested_at;

B4. Documents with confidence below threshold

SELECT e.document_id, e.fields->'effective_date'->>'confidence' AS conf,
       d.doc_class
FROM extractions e
JOIN documents d ON d.document_id = e.document_id
WHERE e.model_version = 'extract-v4.2.1'
  AND (e.fields->'effective_date'->>'confidence')::FLOAT < 0.85
ORDER BY conf;

B5. Full document trail for an agreement

SELECT dal.role, d.doc_class, d.document_id, d.ingested_at, d.source_uri
FROM document_agreement_links dal
JOIN documents d ON d.document_id = dal.document_id
WHERE dal.agreement_id = 'agr_acme_msa_2020'
ORDER BY d.ingested_at;

C · Obligations

C1. Current obligations (latest extraction per agreement+type)

SELECT DISTINCT ON (o.agreement_id, o.obligation_type)
  o.*
FROM obligations o
WHERE o.status != 'superseded'
ORDER BY o.agreement_id, o.obligation_type,
         o.extracted_at DESC, o.model_version DESC;

C2. Upcoming due obligations

SELECT a.agreement_id, s.canonical_name, o.obligation_type, o.value, o.due_at,
       o.due_at - CURRENT_DATE AS days_until_due
FROM obligations o
JOIN agreements a ON a.agreement_id = o.agreement_id
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE o.status = 'pending'
  AND o.due_at BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '60 days'
ORDER BY o.due_at;

C3. Obligations missing required fields

Quality check: payment obligations should have amount and due_at.

SELECT o.obligation_id, o.agreement_id, o.value
FROM obligations o
WHERE o.obligation_type = 'payment'
  AND (o.value IS NULL
       OR o.value->>'amount' IS NULL
       OR o.due_at IS NULL);

C4. Agreements without an indemnification clause

SELECT a.agreement_id, s.canonical_name
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE a.agreement_type = 'master_service_agreement'
  AND NOT EXISTS (
    SELECT 1 FROM obligations o
    WHERE o.agreement_id = a.agreement_id
      AND o.obligation_type = 'indemnification'
      AND o.status != 'superseded'
  );

C5. Liability caps below a threshold

SELECT a.agreement_id, s.canonical_name,
       (o.value->>'cap_amount')::NUMERIC AS cap_amount
FROM obligations o
JOIN agreements a ON a.agreement_id = o.agreement_id
JOIN suppliers s ON s.supplier_id = a.supplier_id
WHERE o.obligation_type = 'liability_cap'
  AND o.status != 'superseded'
  AND (o.value->>'cap_amount')::NUMERIC < 100000;

D · Suppliers & rollups

D1. Supplier hierarchy (root parent per supplier)

WITH RECURSIVE chain AS (
  SELECT supplier_id, supplier_id AS root_id, 0 AS depth
  FROM suppliers WHERE parent_supplier_id IS NULL
  UNION ALL
  SELECT s.supplier_id, c.root_id, c.depth + 1
  FROM suppliers s JOIN chain c ON c.supplier_id = s.parent_supplier_id
)
SELECT supplier_id, root_id, depth FROM chain;

D2. Supplier consolidation candidates (similar names)

Suppliers within trigram similarity threshold of each other; surfaced for human review.

SELECT s1.supplier_id AS a_id, s1.canonical_name AS a_name,
       s2.supplier_id AS b_id, s2.canonical_name AS b_name,
       similarity(s1.canonical_name, s2.canonical_name) AS sim
FROM suppliers s1
JOIN suppliers s2
  ON s1.customer_id = s2.customer_id
 AND s1.supplier_id < s2.supplier_id
 AND s1.canonical_name % s2.canonical_name  -- pg_trgm operator
WHERE similarity(s1.canonical_name, s2.canonical_name) > 0.75
ORDER BY sim DESC;

D3. Spend per supplier (rolled up to parent)

See chapter 06 §operations.

D4. Suppliers with no spend but active agreements

SELECT s.supplier_id, s.canonical_name, a.agreement_id, a.effective_date
FROM suppliers s
JOIN agreements a ON a.supplier_id = s.supplier_id AND a.status = 'active'
WHERE NOT EXISTS (
  SELECT 1 FROM spend sp
  WHERE sp.supplier_id = s.supplier_id
    AND sp.accrued_at >= a.effective_date
);

E · Spend

E1. Off-contract spend per supplier

SELECT s.supplier_id, sp.canonical_name,
       SUM(s.amount) AS off_contract_total,
       s.currency
FROM spend s
JOIN suppliers sp ON sp.supplier_id = s.supplier_id
LEFT JOIN agreements a
  ON a.supplier_id = s.supplier_id
 AND a.status = 'active'
 AND s.accrued_at BETWEEN a.effective_date AND COALESCE(a.current_term_end, '9999-12-31')
WHERE a.agreement_id IS NULL
  AND s.customer_id = 'cust_acme'
GROUP BY s.supplier_id, sp.canonical_name, s.currency
ORDER BY off_contract_total DESC;

E2. Off-contract subcategorized

Distinguish "no agreement ever" from "agreement expired."

WITH off_contract AS (
  SELECT s.*
  FROM spend s
  LEFT JOIN agreements a
    ON a.supplier_id = s.supplier_id
   AND a.status = 'active'
   AND s.accrued_at BETWEEN a.effective_date AND COALESCE(a.current_term_end, '9999-12-31')
  WHERE a.agreement_id IS NULL
)
SELECT
  oc.supplier_id,
  CASE
    WHEN EXISTS (SELECT 1 FROM agreements a2 WHERE a2.supplier_id = oc.supplier_id) THEN 'expired_agreement'
    ELSE 'no_agreement'
  END AS subcategory,
  SUM(oc.amount) AS total
FROM off_contract oc
GROUP BY oc.supplier_id, subcategory;

E3. Spend trend by month

SELECT
  DATE_TRUNC('month', s.accrued_at) AS month,
  SUM(s.amount) AS total_spend
FROM spend s
WHERE s.customer_id = 'cust_acme'
  AND s.currency = 'USD'
GROUP BY 1
ORDER BY 1;

E4. Top suppliers by spend in FY

SELECT s.canonical_name, SUM(sp.amount) AS fy_spend
FROM spend sp
JOIN suppliers s ON s.supplier_id = sp.supplier_id
WHERE sp.customer_id = 'cust_acme'
  AND sp.accrued_at >= '2025-07-01' AND sp.accrued_at < '2026-07-01'
GROUP BY s.canonical_name
ORDER BY fy_spend DESC
LIMIT 20;

F · Quality & auditability

F1. Extraction confidence distribution

Reliability-diagram input.

SELECT
  WIDTH_BUCKET((fields->'effective_date'->>'confidence')::FLOAT, 0, 1, 10) AS bucket,
  COUNT(*) AS n
FROM extractions
WHERE model_version = 'extract-v4.2.1'
GROUP BY bucket
ORDER BY bucket;

F2. Documents where extraction differed between two model versions

SELECT e1.document_id,
       e1.fields->'effective_date'->>'value' AS v_old,
       e2.fields->'effective_date'->>'value' AS v_new
FROM extractions e1
JOIN extractions e2
  ON e1.document_id = e2.document_id
WHERE e1.model_version = 'extract-v4.1.0'
  AND e2.model_version = 'extract-v4.2.1'
  AND COALESCE(e1.fields->'effective_date'->>'value', '') !=
      COALESCE(e2.fields->'effective_date'->>'value', '');

F3. Audit trail for a supplier

SELECT raw_supplier_name, canonical_name, resolution_method, confidence, resolved_at
FROM supplier_name_resolutions snr
JOIN suppliers s ON s.supplier_id = snr.resolved_supplier_id
WHERE snr.customer_id = 'cust_acme'
  AND s.canonical_name = 'Acme Corp'
ORDER BY snr.resolved_at DESC;

F4. dbt test patterns

See chapter 09 for the test taxonomy.

✓ Check yourself

Answer from memory before opening each one.

  1. Pattern A3 joins obligations filtered only on status not superseded. An agreement was extracted by two model versions. What does the query return, and how do you fix it?

    Show answer

    It returns the agreement once for each non-superseded version, and the versions can disagree on auto-renew. Join to the current-obligations pattern instead of the raw table. See agreements and renewals and current obligations.

  2. Pattern C1 uses DISTINCT ON. How does it choose the row, and what breaks if the ORDER BY does not start with the same columns?

    Show answer

    It keeps the first row of each group as the ORDER BY sorts them. Postgres requires the leading sort columns to match the DISTINCT ON expressions, and the extraction time and version columns after them decide which row is first. See obligations.

  3. Pattern F1 buckets confidence from 0 to 1 into ten buckets with WIDTH_BUCKET. Where does a confidence of exactly 1.0 land, and why does it matter?

    Show answer

    In bucket 11, because the upper bound is outside the range. The reliability chart then shows an eleventh bar, and the most confident extractions look like an error. Clamp the value or widen the upper bound slightly. See quality and auditability.

  4. Pattern D2 combines the % operator with a similarity filter. What does each do, and what happens on a large table without a trigram index?

    Show answer

    The % operator tests similarity against a session threshold and can use a trigram index, while the explicit filter tightens it to 0.75. Without the index the self-join compares every pair, which grows with the square of the supplier count. See suppliers and rollups.

  5. Pattern E1 treats a 2023 invoice as off-contract even though it falls inside the term of an agreement that expired in 2024. Why, and how do you fix it?

    Show answer

    The join requires the agreement's current status to be active, and a now-expired agreement never matches. Cover the invoice date by the agreement's term and exclude only agreements that were not in force, such as drafts or ones terminated before that date. See spend.

Takeaway

  • The patterns assume the chapter 01 schema, and each one has gotchas worth reading before you copy it.
  • Any query over obligations should read the current view, or it counts every model version.
  • DISTINCT ON and ROW_NUMBER can both pick the current row, but DISTINCT ON needs its columns to lead the ORDER BY.
  • Off-contract logic must test coverage at the spend date, not the agreement's present status.
  • Trigram search needs an index to stay practical, and a similarity filter alone does not provide one.
  • WIDTH_BUCKET puts the upper bound itself into an extra bucket.

The schema will change, and the next chapter covers how to change it safely. → Schema Evolution Recipes

Further reading