A medieval-market interactive explainer for Old School RuneScape’s Grand Exchange, using the full live item universe plus focused ranked ledgers to teach spread, tax, liquidity, Bonds, market depth, and purchasing power.

Grand Exchange Alpha: A Field Guide to Gielinor's Markets

Matt Smith


import { useState } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";

const N = (v) => (v != null ? Number(v) : 0);

const QUERIES = [
  {
    id: "Q01",
    sql: `-- Q01: Pricing Summary Report
SELECT
    l_returnflag,
    l_linestatus,
    SUM(l_quantity) AS sum_qty,
    SUM(l_extendedprice) AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity) AS avg_qty,
    AVG(l_extendedprice) AS avg_price,
    AVG(l_discount) AS avg_disc,
    COUNT(*) AS count_order
FROM
    lineitem
WHERE
    l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus;`,
  },
  {
    id: "Q02",
    sql: `-- Q02: Minimum Cost Supplier
SELECT
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
FROM
    part,
    supplier,
    partsupp,
    nation,
    region
WHERE
    p_partkey = ps_partkey
    AND s_suppkey = ps_suppkey
    AND p_size = 15
    AND p_type LIKE '%BRASS'
    AND s_nationkey = n_nationkey
    AND n_regionkey = r_regionkey
    AND r_name = 'EUROPE'
    AND ps_supplycost = (
        SELECT MIN(ps_supplycost)
        FROM partsupp, supplier, nation, region
        WHERE p_partkey = ps_partkey
          AND s_suppkey = ps_suppkey
          AND s_nationkey = n_nationkey
          AND n_regionkey = r_regionkey
          AND r_name = 'EUROPE'
    )
ORDER BY
    s_acctbal DESC,
    n_name,
    s_name,
    p_partkey
LIMIT 100;`,
  },
  {
    id: "Q03",
    sql: `-- Q03: Shipping Priority
SELECT
    l_orderkey,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = 'BUILDING'
    AND c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate < DATE '1995-03-15'
    AND l_shipdate > DATE '1995-03-15'
GROUP BY
    l_orderkey,
    o_orderdate,
    o_shippriority
ORDER BY
    revenue DESC,
    o_orderdate
LIMIT 10;`,
  },
  {
    id: "Q04",
    sql: `-- Q04: Order Priority Checking
SELECT
    o_orderpriority,
    COUNT(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT *
        FROM lineitem
        WHERE l_orderkey = o_orderkey
          AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;`,
  },
  {
    id: "Q05",
    sql: `-- Q05: Local Supplier Volume
SELECT
    n_name,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM
    customer,
    orders,
    lineitem,
    supplier,
    nation,
    region
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND l_suppkey = s_suppkey
    AND c_nationkey = s_nationkey
    AND s_nationkey = n_nationkey
    AND n_regionkey = r_regionkey
    AND r_name = 'ASIA'
    AND o_orderdate >= DATE '1994-01-01'
    AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR
GROUP BY
    n_name
ORDER BY
    revenue DESC;`,
  },
  {
    id: "Q06",
    sql: `-- Q06: Forecasting Revenue Change
SELECT
    SUM(l_extendedprice * l_discount) AS revenue
FROM
    lineitem
WHERE
    l_shipdate >= DATE '1994-01-01'
    AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
    AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01
    AND l_quantity < 24;`,
  },
  {
    id: "Q07",
    sql: `-- Q07: Volume Shipping
SELECT
    supp_nation,
    cust_nation,
    l_year,
    SUM(volume) AS revenue
FROM (
    SELECT
        n1.n_name AS supp_nation,
        n2.n_name AS cust_nation,
        EXTRACT(YEAR FROM l_shipdate) AS l_year,
        l_extendedprice * (1 - l_discount) AS volume
    FROM
        supplier,
        lineitem,
        orders,
        customer,
        nation n1,
        nation n2
    WHERE
        s_suppkey = l_suppkey
        AND o_orderkey = l_orderkey
        AND c_custkey = o_custkey
        AND s_nationkey = n1.n_nationkey
        AND c_nationkey = n2.n_nationkey
        AND (
            (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')
            OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
        )
        AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
) AS shipping
GROUP BY
    supp_nation,
    cust_nation,
    l_year
ORDER BY
    supp_nation,
    cust_nation,
    l_year;`,
  },
  {
    id: "Q08",
    sql: `-- Q08: National Market Share
SELECT
    o_year,
    SUM(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / SUM(volume) AS mkt_share
FROM (
    SELECT
        EXTRACT(YEAR FROM o_orderdate) AS o_year,
        l_extendedprice * (1 - l_discount) AS volume,
        n2.n_name AS nation
    FROM
        part,
        supplier,
        lineitem,
        orders,
        customer,
        nation n1,
        nation n2,
        region
    WHERE
        p_partkey = l_partkey
        AND s_suppkey = l_suppkey
        AND l_orderkey = o_orderkey
        AND o_custkey = c_custkey
        AND c_nationkey = n1.n_nationkey
        AND n1.n_regionkey = r_regionkey
        AND r_name = 'AMERICA'
        AND s_nationkey = n2.n_nationkey
        AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
        AND p_type = 'ECONOMY ANODIZED STEEL'
) AS all_nations
GROUP BY
    o_year
ORDER BY
    o_year;`,
  },
  {
    id: "Q09",
    sql: `-- Q09: Product Type Profit Measure
SELECT
    nation,
    o_year,
    SUM(amount) AS sum_profit
FROM (
    SELECT
        n_name AS nation,
        EXTRACT(YEAR FROM o_orderdate) AS o_year,
        l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
    FROM
        part,
        supplier,
        lineitem,
        partsupp,
        orders,
        nation
    WHERE
        s_suppkey = l_suppkey
        AND ps_suppkey = l_suppkey
        AND ps_partkey = l_partkey
        AND p_partkey = l_partkey
        AND o_orderkey = l_orderkey
        AND s_nationkey = n_nationkey
        AND p_name LIKE '%green%'
) AS profit
GROUP BY
    nation,
    o_year
ORDER BY
    nation,
    o_year DESC;`,
  },
  {
    id: "Q10",
    sql: `-- Q10: Returned Item Reporting
SELECT
    c_custkey,
    c_name,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
FROM
    customer,
    orders,
    lineitem,
    nation
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate >= DATE '1993-10-01'
    AND o_orderdate < DATE '1993-10-01' + INTERVAL '3' MONTH
    AND l_returnflag = 'R'
    AND c_nationkey = n_nationkey
GROUP BY
    c_custkey,
    c_name,
    c_acctbal,
    c_phone,
    n_name,
    c_address,
    c_comment
ORDER BY
    revenue DESC
LIMIT 20;`,
  },
  {
    id: "Q11",
    sql: `-- Q11: Important Stock Identification
SELECT
    ps_partkey,
    SUM(ps_supplycost * ps_availqty) AS value
FROM
    partsupp,
    supplier,
    nation
WHERE
    ps_suppkey = s_suppkey
    AND s_nationkey = n_nationkey
    AND n_name = 'GERMANY'
GROUP BY
    ps_partkey
HAVING
    SUM(ps_supplycost * ps_availqty) > (
        SELECT SUM(ps_supplycost * ps_availqty) * 0.0001
        FROM partsupp, supplier, nation
        WHERE ps_suppkey = s_suppkey
          AND s_nationkey = n_nationkey
          AND n_name = 'GERMANY'
    )
ORDER BY
    value DESC;`,
  },
  {
    id: "Q12",
    sql: `-- Q12: Shipping Modes and Order Priority
SELECT
    l_shipmode,
    SUM(CASE WHEN o_orderpriority = '1-URGENT'
                  OR o_orderpriority = '2-HIGH'
             THEN 1 ELSE 0 END) AS high_line_count,
    SUM(CASE WHEN o_orderpriority <> '1-URGENT'
                 AND o_orderpriority <> '2-HIGH'
             THEN 1 ELSE 0 END) AS low_line_count
FROM
    orders,
    lineitem
WHERE
    o_orderkey = l_orderkey
    AND l_shipmode IN ('MAIL', 'SHIP')
    AND l_commitdate < l_receiptdate
    AND l_shipdate < l_commitdate
    AND l_receiptdate >= DATE '1994-01-01'
    AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR
GROUP BY
    l_shipmode
ORDER BY
    l_shipmode;`,
  },
  {
    id: "Q13",
    sql: `-- Q13: Customer Distribution
SELECT
    c_count,
    COUNT(*) AS custdist
FROM (
    SELECT
        c_custkey,
        COUNT(o_orderkey) AS c_count
    FROM
        customer
        LEFT OUTER JOIN orders ON (
            c_custkey = o_custkey
            AND o_comment NOT LIKE '%special%requests%'
        )
    GROUP BY
        c_custkey
) AS c_orders
GROUP BY
    c_count
ORDER BY
    custdist DESC,
    c_count DESC;`,
  },
  {
    id: "Q14",
    sql: `-- Q14: Promotion Effect
SELECT
    100.00 * SUM(
        CASE WHEN p_type LIKE 'PROMO%'
             THEN l_extendedprice * (1 - l_discount)
             ELSE 0
        END
    ) / SUM(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM
    lineitem,
    part
WHERE
    l_partkey = p_partkey
    AND l_shipdate >= DATE '1995-09-01'
    AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH;`,
  },
  {
    id: "Q15",
    sql: `-- Q15: Top Supplier
WITH revenue AS (
    SELECT
        l_suppkey AS supplier_no,
        SUM(l_extendedprice * (1 - l_discount)) AS total_revenue
    FROM
        lineitem
    WHERE
        l_shipdate >= DATE '1996-01-01'
        AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
    GROUP BY
        l_suppkey
)
SELECT
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
FROM
    supplier,
    revenue
WHERE
    s_suppkey = supplier_no
    AND total_revenue = (SELECT MAX(total_revenue) FROM revenue)
ORDER BY
    s_suppkey;`,
  },
  {
    id: "Q16",
    sql: `-- Q16: Parts/Supplier Relationship
SELECT
    p_brand,
    p_type,
    p_size,
    COUNT(DISTINCT ps_suppkey) AS supplier_cnt
FROM
    partsupp,
    part
WHERE
    p_partkey = ps_partkey
    AND p_brand <> 'Brand#45'
    AND p_type NOT LIKE 'MEDIUM POLISHED%'
    AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
    AND ps_suppkey NOT IN (
        SELECT s_suppkey
        FROM supplier
        WHERE s_comment LIKE '%Customer%Complaints%'
    )
GROUP BY
    p_brand,
    p_type,
    p_size
ORDER BY
    supplier_cnt DESC,
    p_brand,
    p_type,
    p_size;`,
  },
  {
    id: "Q17",
    sql: `-- Q17: Small-Quantity-Order Revenue
SELECT
    SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
    lineitem,
    part
WHERE
    p_partkey = l_partkey
    AND p_brand = 'Brand#23'
    AND p_container = 'MED BOX'
    AND l_quantity < (
        SELECT 0.2 * AVG(l_quantity)
        FROM lineitem
        WHERE l_partkey = p_partkey
    );`,
  },
  {
    id: "Q18",
    sql: `-- Q18: Large Volume Customer
SELECT
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    SUM(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT l_orderkey
        FROM lineitem
        GROUP BY l_orderkey
        HAVING SUM(l_quantity) > 300
    )
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
ORDER BY
    o_totalprice DESC,
    o_orderdate
LIMIT 100;`,
  },
  {
    id: "Q19",
    sql: `-- Q19: Discounted Revenue
SELECT
    SUM(l_extendedprice * (1 - l_discount)) AS revenue
FROM
    lineitem,
    part
WHERE
    (
        p_partkey = l_partkey
        AND p_brand = 'Brand#12'
        AND p_container IN ('SM CASE', 'SM BOX', 'SM TRUNK', 'SM PACK')
        AND l_quantity >= 1 AND l_quantity <= 1 + 10
        AND p_size BETWEEN 1 AND 5
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    )
    OR (
        p_partkey = l_partkey
        AND p_brand = 'Brand#23'
        AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        AND l_quantity >= 10 AND l_quantity <= 10 + 10
        AND p_size BETWEEN 1 AND 10
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    )
    OR (
        p_partkey = l_partkey
        AND p_brand = 'Brand#34'
        AND p_container IN ('LG CASE', 'LG BOX', 'LG TRUNK', 'LG PACK')
        AND l_quantity >= 20 AND l_quantity <= 20 + 10
        AND p_size BETWEEN 1 AND 15
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON'
    );`,
  },
  {
    id: "Q20",
    sql: `-- Q20: Potential Part Promotion
SELECT
    s_name,
    s_address
FROM
    supplier,
    nation
WHERE
    s_suppkey IN (
        SELECT ps_suppkey
        FROM partsupp
        WHERE ps_partkey IN (
            SELECT p_partkey
            FROM part
            WHERE p_name LIKE 'forest%'
        )
        AND ps_availqty > (
            SELECT 0.5 * SUM(l_quantity)
            FROM lineitem
            WHERE l_partkey = ps_partkey
              AND l_suppkey = ps_suppkey
              AND l_shipdate >= DATE '1994-01-01'
              AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
        )
    )
    AND s_nationkey = n_nationkey
    AND n_name = 'CANADA'
ORDER BY
    s_name;`,
  },
  {
    id: "Q21",
    sql: `-- Q21: Suppliers Who Kept Orders Waiting
SELECT
    s_name,
    COUNT(*) AS numwait
FROM
    supplier,
    lineitem l1,
    orders,
    nation
WHERE
    s_suppkey = l1.l_suppkey
    AND o_orderkey = l1.l_orderkey
    AND o_orderstatus = 'F'
    AND l1.l_receiptdate > l1.l_commitdate
    AND EXISTS (
        SELECT *
        FROM lineitem l2
        WHERE l2.l_orderkey = l1.l_orderkey
          AND l2.l_suppkey <> l1.l_suppkey
    )
    AND NOT EXISTS (
        SELECT *
        FROM lineitem l3
        WHERE l3.l_orderkey = l1.l_orderkey
          AND l3.l_suppkey <> l1.l_suppkey
          AND l3.l_receiptdate > l3.l_commitdate
    )
    AND s_nationkey = n_nationkey
    AND n_name = 'SAUDI ARABIA'
GROUP BY
    s_name
ORDER BY
    numwait DESC,
    s_name
LIMIT 100;`,
  },
  {
    id: "Q22",
    sql: `-- Q22: Global Sales Opportunity
SELECT
    cntrycode,
    COUNT(*) AS numcust,
    SUM(c_acctbal) AS totacctbal
FROM (
    SELECT
        SUBSTRING(c_phone FROM 1 FOR 2) AS cntrycode,
        c_acctbal
    FROM
        customer
    WHERE
        SUBSTRING(c_phone FROM 1 FOR 2) IN ('13', '31', '23', '29', '30', '18', '17')
        AND c_acctbal > (
            SELECT AVG(c_acctbal)
            FROM customer
            WHERE c_acctbal > 0.00
              AND SUBSTRING(c_phone FROM 1 FOR 2) IN ('13', '31', '23', '29', '30', '18', '17')
        )
        AND NOT EXISTS (
            SELECT *
            FROM orders
            WHERE o_custkey = c_custkey
        )
) AS custsale
GROUP BY
    cntrycode
ORDER BY
    cntrycode;`,
  },
];

export default function TPCHQueryReference() {
  const [openId, setOpenId] = useState(null);
  const [copied, setCopied] = useState(null);

  // Minimal query to satisfy dive validation
  const { data: _ping } = useSQLQuery("SELECT 1 AS ping");

  const toggle = (id) => setOpenId(openId === id ? null : id);

  const handleCopy = async (id, sql) => {
    try {
      await navigator.clipboard.writeText(sql);
      setCopied(id);
      setTimeout(() => setCopied(null), 1500);
    } catch {
      // ignore
    }
  };

  return (
    <div style={{ background: "#1e1e1e", minHeight: "100vh", fontFamily: "monospace", color: "#e0e0e0" }}>
      {/* Header */}
      <div style={{ background: "#181818", borderBottom: "2px solid #ff6900", padding: "20px 28px" }}>
        <h1 style={{ margin: 0, fontSize: "20px", fontWeight: 700, color: "#fff100", letterSpacing: "0.04em" }}>
          TPC-H Query Reference
        </h1>
        <p style={{ margin: "4px 0 0", fontSize: "12px", color: "#888" }}>
          22 standard benchmark queries
        </p>
      </div>

      {/* Accordion */}
      <div style={{ padding: "16px 20px", maxWidth: "900px", margin: "0 auto" }}>
        {QUERIES.map((q) => {
          const isOpen = openId === q.id;
          return (
            <div
              key={q.id}
              style={{
                marginBottom: "6px",
                border: isOpen ? "1px solid #ff6900" : "1px solid #333",
                borderRadius: "4px",
                overflow: "hidden",
              }}
            >
              {/* Accordion header */}
              <button
                onClick={() => toggle(q.id)}
                style={{
                  width: "100%",
                  display: "flex",
                  alignItems: "center",
                  justifyContent: "space-between",
                  padding: "10px 16px",
                  background: isOpen ? "#252525" : "#1e1e1e",
                  border: "none",
                  cursor: "pointer",
                  color: isOpen ? "#fff100" : "#c0c0c0",
                  fontSize: "13px",
                  fontWeight: 600,
                  fontFamily: "monospace",
                  letterSpacing: "0.06em",
                  textAlign: "left",
                }}
              >
                <span>{q.id}</span>
                <span style={{ fontSize: "16px", color: "#ff6900", lineHeight: 1 }}>
                  {isOpen ? "▲" : "▼"}
                </span>
              </button>

              {/* Accordion body */}
              {isOpen && (
                <div style={{ background: "#141414", position: "relative" }}>
                  <button
                    onClick={() => handleCopy(q.id, q.sql)}
                    style={{
                      position: "absolute",
                      top: "10px",
                      right: "14px",
                      background: copied === q.id ? "#2d7a00" : "#2a2a2a",
                      border: "1px solid #444",
                      borderRadius: "3px",
                      color: copied === q.id ? "#fff" : "#aaa",
                      fontSize: "11px",
                      padding: "3px 10px",
                      cursor: "pointer",
                      fontFamily: "monospace",
                      zIndex: 1,
                    }}
                  >
                    {copied === q.id ? "✓ copied" : "copy"}
                  </button>
                  <pre
                    style={{
                      margin: 0,
                      padding: "14px 16px",
                      fontSize: "12px",
                      lineHeight: "1.7",
                      overflowX: "auto",
                      color: "#d4d4d4",
                      whiteSpace: "pre",
                    }}
                  >
                    <code>{q.sql}</code>
                  </pre>
                </div>
              )}
            </div>
          );
        })}
      </div>
    </div>
  );
}


All 22 TPC-H benchmark queries in a collapsible accordion interface with copy buttons.

TPC-H Query Reference

gwdean

Every U.S. Senator plotted by campaign money raised vs. bills sponsored in the 119th Congress — faces are the data points. Click one to see who funds them (PACs/individuals/small-dollar), which states the money comes from, uncapped Super PAC money for and against them, top organization donors, committee seats, and recent bills. Story-lead leaderboards surface the patterns; filter by party and state. Built on FEC (campaign finance, Schedule A by-state, Schedule E independent expenditures) + Congress.gov data.

Money In, Law Out

I want to compete in the MotherDuck DiveMaxxing hackathon. Here's a concept doc — a "money to law" idea joining FEC campaign finance to Congress.gov legislative activity. Go for the Most Creative category.

Scope it to the U.S. Senate first, with bills sponsored (119th Congress) as the Y axis.

Use a photo of each member of Congress instead of a dot on the scatter.

When someone clicks a senator, pop up a modal with more data — what's engaging and impactful to include?

Use MotherDuck's dark theme/styling for the whole Dive.

Are we pulling corporations, PACs, and top donors for each senator too?

As a political journalist, what would you want to know from this data to discover potential stories — and how should we display it?

Show the top 3–5 per-story leads, not just the #1 — surface the pattern, not a single outlier.

Get the out-of-state donation details from the FEC — which states each senator's money comes from.

Tarik Moody

Year-over-year and monthly trend views of Wisconsin single-family home median prices and estimated monthly payments (10% down, 30-yr fixed). Filterable by county. Includes P25–P75 IQR band, 30-yr rate trend, and top corporate buyers table. Data: RETR filings 2021–2026 + FRED MORTGAGE30US.

Wisconsin Single-Family Home Affordability

Cole Tenold

Interactive interpretability dashboard for the XGBoost Telco churn model: headline holdout metrics, an interactive decision-cutoff slider, live confusion matrix + precision/recall/F1, probability distribution, ROC & PR curves, feature importance (mean|SHAP| / gain / weight / cover), a SHAP beeswarm, and a calibration/reliability plot. Reads telco_churn.ml.* (latest model run).

Machine Learning Interpretability Dashboard

Use a MotherDuck flight to pull the Telco Customer Churn dataset from IBM's public GitHub mirror into MotherDuck. Design it to be an incremental load that is idempotent (no duplication even if I re-run it). Use data engineering best practices including logging, retries with exponential backoff, and others that you decide are helpful (do some research!). Then use XGBoost to predict churn or not within that Flight (research XGBoost best practices and data science best practices including how to use train/validate/holdout splits and prevent target leakage). Then, create a MotherDuck Dive to help me interpret the machine learning model (research machine learning interpretability best practices) including but not limited to the ROC curve, confusion matrix, shapley values, feature importance plots, etc. I also want to be able to tune the cutoff point and see the impact on the metrics. Be sure to store the predictions and other metrics about the quality of the model in a MotherDuck database. Make the dive beautiful. Make sure that there is no chart junk (and use some color when it is helpful).

In the dive, remove: “XGBoost on the IBM Telco dataset. Metrics on the holdout split. Model 74dd9f6e · trained 2026-06-18 13:16 UTC · XGBoost 2.1.4 · SHAP & importances computed on the holdout.”
Also remove: Decision cutoff Move the cutoff to trade recall against precision. Everything below this control updates live.
Also remove: all the metrics in the first row 
Then, swap the position of the ROC curve and the "Predicted probability by actual outcome" charts.
Then, remove the legend of the confusion matrix
Put the "Accuracy" metric to the left of the metrics instead of to the far right. It should be the first one.

Put the confusion matrix, ROC curve, and the SHAP summary charts in the first row of charts. Confusion matrix should get 25% of the width, ROC curve should get 25%, then the SHAP should get 50%.

Instead of the categorical values being grey in the SHAP plot, make each category its own color. Do not use red or blue though, since that would be confusing.

  Remove this from the description: Color = feature value (low → high).

  Put a description under the confusion matrix header: "Adjust cutoff to balance False Positives and False Negatives"

  Align the bottom of the confusion matrix, ROC curve, and SHAP plot so that they look approximately the same height. Make the confusion matrix into a square with 4 equally sized boxes in it (height = width for the boxes).

Put the numeric SHAP features back to using the red to blue scale from before. Change the circles to have transparent fill (no fill), but have a colored outline instead. Make the outline partially transparent as well.

Alex Monahan

import { useState } from "react";
import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query";
import { RadarChart, PolarGrid, PolarAngleAxis, PolarRadiusAxis, Radar, Legend, Tooltip, ResponsiveContainer } from "recharts";


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/my_db/d13e03e2-91a9-4cd9-a9a0-f44b1134da9a', alias: 'my_db' }
];

const N = (v) => (v != null ? Number(v) : 0);

const TEAM_A_COLOR = "#0777b3";
const TEAM_B_COLOR = "#bd4e35";

const TEAMS = [
  "Argentina", "Australia", "Belgium", "Brazil", "Cameroon", "Canada", "Costa Rica",
  "Croatia", "Denmark", "Ecuador", "England", "France", "Germany", "Ghana", "IR Iran",
  "Japan", "Korea Republic", "Mexico", "Morocco", "Netherlands", "Poland", "Portugal",
  "Qatar", "Saudi Arabia", "Senegal", "Serbia", "Spain", "Switzerland", "Tunisia",
  "United States", "Uruguay", "Wales",
];

// Tournament-wide min/max per metric (single-match values), used to normalize each axis to 0-100
const RANGES = {
  xg: { min: 0, max: 5 },
  sot: { min: 0, max: 13 },
  total_shots: { min: 0, max: 32 },
  corners: { min: 0, max: 14 },
  crosses: { min: 2, max: 46 },
  possession: { min: 23, max: 81 },
  pass_accuracy: { min: 55, max: 95 },
  touches: { min: 341, max: 1190 },
  long_balls: { min: 39, max: 106 },
  tackles: { min: 5, max: 33 },
  interceptions: { min: 2, max: 19 },
  aerials_won: { min: 5, max: 33 },
  clearances: { min: 3, max: 50 },
  saves: { min: 0, max: 10 },
  fouls: { min: 3, max: 30 },
  offsides: { min: 0, max: 10 },
};

function normalize(value, metric) {
  const { min, max } = RANGES[metric];
  if (max === min) return 50;
  const pct = ((value - min) / (max - min)) * 100;
  return Math.round(Math.max(0, Math.min(100, pct)));
}

const AXIS_LABELS = {
  xg: "xG",
  sot: "Shots on Target",
  total_shots: "Total Shots",
  corners: "Corners",
  crosses: "Crosses",
  possession: "Possession",
  pass_accuracy: "Pass Accuracy",
  touches: "Touches",
  long_balls: "Long Balls",
  tackles: "Tackles",
  interceptions: "Interceptions",
  aerials_won: "Aerials Won",
  clearances: "Clearances",
  saves: "Saves",
  fouls: "Fouls",
  offsides: "Offsides",
};

const OFFENSE_METRICS = ["xg", "sot", "total_shots", "corners", "crosses", "possession", "pass_accuracy", "touches", "long_balls"];
const DEFENSE_METRICS = ["tackles", "interceptions", "aerials_won", "clearances", "saves", "fouls", "offsides"];

export default function TeamDnaComparison() {
  const [showAbout, setShowAbout] = useState(false);
  const [teamA, setTeamA] = useDiveState("teamA", "Argentina");
  const [teamB, setTeamB] = useDiveState("teamB", "France");
  const [matchIdxA, setMatchIdxA] = useDiveState("matchIdxA", 0);
  const [matchIdxB, setMatchIdxB] = useDiveState("matchIdxB", 0);

  // Per-match stats for every team, ordered by match date within team
  const matchesQuery = useSQLQuery(`
    WITH team_matches AS (
      SELECT match, strftime(match_time, '%b %d') AS match_date, home_team AS team, away_team AS opponent,
             home_xg AS xg, home_sot AS sot, home_total_shots AS total_shots,
             home_corners AS corners, home_crosses AS crosses,
             home_possession AS possession,
             home_completed_passes AS comp_passes, home_attempted_pases AS att_passes,
             home_touches AS touches, home_long_balls AS long_balls,
             home_tackles AS tackles, home_interceptions AS interceptions,
             home_aerials_won AS aerials_won, home_clearances AS clearances,
             home_saves AS saves, home_fouls AS fouls, home_offsides AS offsides
      FROM "my_db"."main"."fifa_match_2022"
      UNION ALL
      SELECT match, strftime(match_time, '%b %d') AS match_date, away_team AS team, home_team AS opponent,
             away_xg AS xg, away_sot AS sot, away_total_shots AS total_shots,
             away_corners AS corners, away_crosses AS crosses,
             away_possession AS possession,
             away_completed_passes AS comp_passes, away_attempted_pases AS att_passes,
             away_touches AS touches, away_long_balls AS long_balls,
             away_tackles AS tackles, away_interceptions AS interceptions,
             away_aerials_won AS aerials_won, away_clearances AS clearances,
             away_saves AS saves, away_fouls AS fouls, away_offsides AS offsides
      FROM "my_db"."main"."fifa_match_2022"
    )
    SELECT team, match, match_date, opponent,
           xg, sot, total_shots, corners, crosses,
           possession, ROUND(100.0 * comp_passes / att_passes, 1) AS pass_accuracy,
           touches, long_balls,
           tackles, interceptions, aerials_won, clearances, saves, fouls, offsides
    FROM team_matches
    ORDER BY team, match
  `);

  const allRows = Array.isArray(matchesQuery.data) ? matchesQuery.data : [];

  const matchesByTeam = {};
  allRows.forEach((r) => {
    if (!matchesByTeam[r.team]) matchesByTeam[r.team] = [];
    matchesByTeam[r.team].push(r);
  });

  const matchesA = matchesByTeam[teamA] || [];
  const matchesB = matchesByTeam[teamB] || [];

  const safeIdxA = Math.min(N(matchIdxA), Math.max(0, matchesA.length - 1));
  const safeIdxB = Math.min(N(matchIdxB), Math.max(0, matchesB.length - 1));

  const matchA = matchesA[safeIdxA];
  const matchB = matchesB[safeIdxB];

  function buildRadarData(metricList) {
    return metricList.map((metric) => ({
      metric: AXIS_LABELS[metric],
      [teamA]: matchA ? normalize(N(matchA[metric]), metric) : 0,
      [teamB]: matchB ? normalize(N(matchB[metric]), metric) : 0,
    }));
  }

  const offenseData = buildRadarData(OFFENSE_METRICS);
  const defenseData = buildRadarData(DEFENSE_METRICS);

  return (
    <div className="p-6" style={{ background: "#f8f8f8", margin: "0 auto" }}>
      <h1 className="text-2xl font-semibold mt-4" style={{ color: "#231f20" }}>
        FIFA World Cup Team DNA Comparison
      </h1>
      <p className="text-sm mb-2" style={{ color: "#6a6a6a" }}>
        Match-by-match tactical profile, World Cup 2022
      </p>

      <button
        onClick={() => setShowAbout(!showAbout)}
        className="text-xs mb-4"
        style={{ color: "#0777b3", background: "none", border: "none", padding: 0, cursor: "pointer", textDecoration: "underline" }}
      >
        {showAbout ? "Hide" : "About this dive"}
      </button>

      {showAbout && (
        <div className="text-sm mb-6" style={{ color: "#231f20", lineHeight: 1.6 }}>
          <p className="mb-2">
            Compare any two World Cup 2022 teams' tactical "DNA" head-to-head, match by match.
            Each team has its own slider to step through their individual matches (group stage
            through the final) — pick any two matches, even from different stages of the
            tournament, and see how each team's playing style overlaps or contrasts.
          </p>
          <p className="mb-2">
            Two radar charts split the comparison into Offense and Defense so each stays readable:
          </p>
          <ul className="mb-2" style={{ paddingLeft: "1.2em", listStyleType: "disc" }}>
            <li>Offense (9 metrics): xG, Shots on Target, Total Shots, Corners, Crosses, Possession, Pass Accuracy, Touches, Long Balls</li>
            <li>Defense (7 metrics): Tackles, Interceptions, Aerials Won, Clearances, Saves, Fouls, Offsides</li>
          </ul>
          <p className="mb-2">
            All 16 values are normalized to a 0–100 scale, based on the tournament-wide min/max
            for that metric, so metrics on very different scales — like xG (0–5) and Touches
            (300–1200) — can sit on the same chart and be compared visually as shape, not raw numbers.
          </p>
          <p>
            Use it to spot stylistic mismatches (e.g. a high-possession, high-pass-accuracy team
            vs. a low-possession, high-tackle counter-attacking team) or to trace how a single
            team's tactical shape evolved from their opening group match to a knockout-stage
            performance.
          </p>
        </div>
      )}

      {/* Team A controls */}
      <div className="mb-4">
        <div className="flex items-center gap-3">
          <select
            value={teamA}
            onChange={(e) => { setTeamA(e.target.value); setMatchIdxA(0); }}
            className="text-sm"
            style={{ color: TEAM_A_COLOR, fontWeight: 600, border: "1px solid #ddd", borderRadius: "4px", padding: "4px 8px" }}
          >
            {TEAMS.map((t) => (
              <option key={t} value={t}>{t}</option>
            ))}
          </select>
          <input
            type="range"
            min={0}
            max={Math.max(0, matchesA.length - 1)}
            value={safeIdxA}
            onChange={(e) => setMatchIdxA(Number(e.target.value))}
            style={{ flex: 1, accentColor: TEAM_A_COLOR }}
          />
        </div>
        <p className="text-xs mt-1" style={{ color: "#6a6a6a" }}>
          {matchA ? `vs ${matchA.opponent} (${matchA.match_date})` : "No match data"}
        </p>
      </div>

      {/* Team B controls */}
      <div className="mb-6">
        <div className="flex items-center gap-3">
          <select
            value={teamB}
            onChange={(e) => { setTeamB(e.target.value); setMatchIdxB(0); }}
            className="text-sm"
            style={{ color: TEAM_B_COLOR, fontWeight: 600, border: "1px solid #ddd", borderRadius: "4px", padding: "4px 8px" }}
          >
            {TEAMS.map((t) => (
              <option key={t} value={t}>{t}</option>
            ))}
          </select>
          <input
            type="range"
            min={0}
            max={Math.max(0, matchesB.length - 1)}
            value={safeIdxB}
            onChange={(e) => setMatchIdxB(Number(e.target.value))}
            style={{ flex: 1, accentColor: TEAM_B_COLOR }}
          />
        </div>
        <p className="text-xs mt-1" style={{ color: "#6a6a6a" }}>
          {matchB ? `vs ${matchB.opponent} (${matchB.match_date})` : "No match data"}
        </p>
      </div>

      {/* Two radar charts side by side: Offense and Defense */}
      <div className="grid grid-cols-2 gap-4">
        <div>
          <h2 className="text-sm font-semibold mb-1 text-center" style={{ color: "#231f20" }}>Offense</h2>
          {matchesQuery.isLoading ? (
            <div className="bg-gray-100 animate-pulse rounded" style={{ height: 280 }} />
          ) : (
            <ResponsiveContainer width="100%" height={280}>
              <RadarChart data={offenseData}>
                <PolarGrid stroke="#ddd" />
                <PolarAngleAxis dataKey="metric" fontSize={10} />
                <PolarRadiusAxis angle={90} domain={[0, 100]} tick={false} axisLine={false} />
                <Radar name={teamA} dataKey={teamA} stroke={TEAM_A_COLOR} fill={TEAM_A_COLOR} fillOpacity={0.35} strokeWidth={2} />
                <Radar name={teamB} dataKey={teamB} stroke={TEAM_B_COLOR} fill={TEAM_B_COLOR} fillOpacity={0.35} strokeWidth={2} />
                <Tooltip />
              </RadarChart>
            </ResponsiveContainer>
          )}
        </div>
        <div>
          <h2 className="text-sm font-semibold mb-1 text-center" style={{ color: "#231f20" }}>Defense</h2>
          {matchesQuery.isLoading ? (
            <div className="bg-gray-100 animate-pulse rounded" style={{ height: 280 }} />
          ) : (
            <ResponsiveContainer width="100%" height={280}>
              <RadarChart data={defenseData}>
                <PolarGrid stroke="#ddd" />
                <PolarAngleAxis dataKey="metric" fontSize={10} />
                <PolarRadiusAxis angle={90} domain={[0, 100]} tick={false} axisLine={false} />
                <Radar name={teamA} dataKey={teamA} stroke={TEAM_A_COLOR} fill={TEAM_A_COLOR} fillOpacity={0.35} strokeWidth={2} />
                <Radar name={teamB} dataKey={teamB} stroke={TEAM_B_COLOR} fill={TEAM_B_COLOR} fillOpacity={0.35} strokeWidth={2} />
                <Tooltip />
              </RadarChart>
            </ResponsiveContainer>
          )}
        </div>
      </div>

      {!matchesQuery.isLoading && (
        <div className="flex justify-center gap-6 mt-2 text-sm">
          <span style={{ color: TEAM_A_COLOR, fontWeight: 600 }}>● {teamA}</span>
          <span style={{ color: TEAM_B_COLOR, fontWeight: 600 }}>● {teamB}</span>
        </div>
      )}
    </div>
  );
}


Compare any two World Cup 2022 teams' tactical "DNA" head-to-head, match by match. Each team has its own slider to step through their individual matches (group stage through the final) — choose any two matches, even from different stages of the tournament, and see how each team's playing style overlaps or contrasts.

Two radar charts split the comparison into Offense and Defense so each stays readable:
- Offense (9 metrics): xG, Shots on Target, Total Shots, Corners, Crosses, Possession, Pass Accuracy, Touches, Long Balls
- Defense (7 metrics): Tackles, Interceptions, Aerials Won, Clearances, Saves, Fouls, Offsides

All 16 values are normalized to a 0–100 scale (based on the tournament-wide min/max for that metric) so metrics on very different scales — like xG (0–5) and Touches (300–1200) — can sit on the same chart and be compared visually as shape, not raw numbers.

Use it to spot stylistic mismatches (e.g. a high-possession, high-pass-accuracy team vs. a low-possession, high-tackle counter-attacking team) or to trace how a single team's tactical shape evolved from their opening group match to a knockout-stage performance.

FIFA World Cup Team DNA Comparison

David Fung

Live wait times + Lightning Lane for all 6 Walt Disney World Orlando parks, pulled from ThemeParks.wiki on load. Zoom/pan map with per-park framing, age-based icons, live location + compass heading, and a never/sometime/ASAP ride-ranking filter & sort. Mobile + desktop.

Dive Gallery