What one SQL query shows

The query

One query_corpus call. Eight Supreme Court decisions grouped into four motions, joined through the citation graph to every citing opinion and its court, then pivoted by circuit. 12 rows in 12.7 seconds.

WITH playbook AS (
  SELECT opinion_id,
         CASE citation_key
           WHEN '131sct2846' THEN 'jurisdiction' WHEN '134sct746' THEN 'jurisdiction' WHEN '592us351' THEN 'jurisdiction'
           WHEN '133sct1426' THEN 'class_cert'   WHEN '136sct1036' THEN 'class_cert'
           WHEN '141sct2190' THEN 'standing'
           WHEN '138sct1612' THEN 'arbitration'  WHEN '596us639' THEN 'arbitration'
         END AS motion
  FROM decisions
  WHERE citation_key IN ('131sct2846', '134sct746', '592us351', '133sct1426',
                         '136sct1036', '141sct2190', '138sct1612', '596us639')
),
citing AS (
  SELECT DISTINCT ct.circuit, ct.level, o.opinion_id, p.motion
  FROM citations x
  JOIN playbook p ON p.opinion_id = x.cited_opinion_id
  JOIN opinions o ON o.opinion_id = x.citing_opinion_id
  JOIN courts ct ON ct.court_id = o.court_id
  WHERE ct.jurisdiction = 'federal'
    AND ct.level IN ('appellate', 'trial')
    AND ct.circuit <> 'federal'
    AND o.decided_on >= DATE '2022-01-01'
)
SELECT circuit,
       count(DISTINCT opinion_id) AS opinions,
       count(DISTINCT opinion_id) FILTER (WHERE level = 'appellate') AS appellate,
       count(DISTINCT opinion_id) FILTER (WHERE motion = 'jurisdiction') AS jurisdiction,
       count(DISTINCT opinion_id) FILTER (WHERE motion = 'class_cert') AS class_cert,
       count(DISTINCT opinion_id) FILTER (WHERE motion = 'standing') AS standing,
       count(DISTINCT opinion_id) FILTER (WHERE motion = 'arbitration') AS arbitration,
       rank() OVER (ORDER BY count(DISTINCT opinion_id) DESC) AS volume_rank
FROM citing
GROUP BY circuit
ORDER BY volume_rank

The follow-up: the opinions behind the standing column

The agent’s next call. Published circuit opinions since 2022 that cite TransUnion, ranked by how often they are cited themselves. 8 rows in 2 seconds. From here, get_opinion_text opens any of them.