import React, { useMemo, useState } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
  Area, AreaChart, Bar, BarChart, CartesianGrid, Cell, ComposedChart,
  Legend, Line, ResponsiveContainer, Scatter, ScatterChart, Tooltip, XAxis, YAxis
} from "recharts";

const COLORS = {
  bg: "#070707",
  panel: "#111318",
  panel2: "#171a21",
  duck: "#FFD84D",
  blue: "#4DA3FF",
  green: "#47E6A0",
  red: "#FF5C5C",
  orange: "#FF9F43",
  purple: "#A78BFA",
  text: "#F7F3DF",
  muted: "#9CA3AF",
  grid: "#2A2F3A"
};

function sqlSite(site) {
  return site === "Site B" ? "Site B" : "Site A";
}

const SHARE_URL = "md:_share/gsc_anonymous_dive_public/4154ec92-abe0-400c-86e1-6d1c1645e1a7";

function useRows(sql) {
  // The Gallery sandbox does not automatically attach required_resources yet, so
  // attach the unrestricted public share inside each query before reading it.
  const { data, loading, error } = useSQLQuery(`ATTACH IF NOT EXISTS '${SHARE_URL}' AS gsc_anonymous_dive; ${sql}`);
  return { rows: data || [], loading, error };
}

function Card({ label, value, hint, color = COLORS.duck }) {
  return <div style={{ background: COLORS.panel, border: `1px solid ${COLORS.grid}`, borderRadius: 18, padding: 18 }}>
    <div style={{ color: COLORS.muted, fontSize: 12, letterSpacing: 1, textTransform: "uppercase" }}>{label}</div>
    <div style={{ color, fontSize: 34, fontWeight: 900, marginTop: 6 }}>{value}</div>
    <div style={{ color: COLORS.muted, fontSize: 12, marginTop: 6 }}>{hint}</div>
  </div>;
}

function Panel({ title, subtitle, children }) {
  return <section style={{ background: COLORS.panel, border: `1px solid ${COLORS.grid}`, borderRadius: 22, padding: 20, marginTop: 18 }}>
    <div style={{ display: "flex", justifyContent: "space-between", gap: 12, marginBottom: 14 }}>
      <div>
        <h2 style={{ color: COLORS.text, fontSize: 22, margin: 0 }}>{title}</h2>
        {subtitle && <p style={{ color: COLORS.muted, margin: "6px 0 0", fontSize: 13 }}>{subtitle}</p>}
      </div>
    </div>
    {children}
  </section>;
}

function MiniBadge({ children, color = COLORS.duck }) {
  return <span style={{ border: `1px solid ${color}`, color, padding: "4px 9px", borderRadius: 999, fontSize: 12, fontWeight: 700 }}>{children}</span>;
}

export default function Dive() {
  const [site, setSite] = useState("Site A");
  const s = sqlSite(site);

  const daily = useRows(`
    SELECT date, clicks_index, impressions_index, ctr_pct, avg_position, opportunity_index
    FROM gsc_anonymous_dive.main.daily_performance
    WHERE site_id = '${s}'
    ORDER BY date
  `);

  const opportunities = useRows(`
    SELECT query_id, intent_class, query_length_bucket, clicks_index, impressions_index,
           ctr_pct, avg_position, opportunity_score, rank_zone
    FROM gsc_anonymous_dive.main.query_opportunities
    WHERE site_id = '${s}' AND rank_zone = 'striking distance'
    ORDER BY opportunity_score DESC
    LIMIT 12
  `);

  const intents = useRows(`
    SELECT intent_class, round(sum(impressions_index), 2) AS demand_index,
           round(avg(ctr_pct), 2) AS ctr_pct,
           round(avg(avg_position), 2) AS avg_position
    FROM gsc_anonymous_dive.main.query_opportunities
    WHERE site_id = '${s}'
    GROUP BY intent_class
    ORDER BY demand_index DESC
  `);

  const templates = useRows(`
    SELECT page_template, count(*) AS pages,
           round(avg(opportunity_index), 2) AS opportunity_index,
           round(avg(ctr_pct), 2) AS ctr_pct,
           round(avg(avg_position), 2) AS avg_position
    FROM gsc_anonymous_dive.main.page_performance
    WHERE site_id = '${s}'
    GROUP BY page_template
    ORDER BY opportunity_index DESC
  `);

  const device = useRows(`
    SELECT intent_class, device, impressions_index, avg_position
    FROM gsc_anonymous_dive.main.device_intent_performance
    WHERE site_id = '${s}'
    ORDER BY impressions_index DESC
  `);

  const regions = useRows(`
    SELECT region, impressions_index, clicks_index
    FROM gsc_anonymous_dive.main.region_performance
    WHERE site_id = '${s}'
    ORDER BY impressions_index DESC
  `);

  const kpis = useMemo(() => {
    const rows = daily.rows;
    const last = rows[rows.length - 1] || {};
    const avg = (key) => rows.length ? rows.reduce((a, r) => a + Number(r[key] || 0), 0) / rows.length : 0;
    const opp = avg("opportunity_index");
    const ctr = avg("ctr_pct");
    const pos = avg("avg_position");
    return {
      demand: Math.round(last.impressions_index || 0),
      clicks: Math.round(last.clicks_index || 0),
      ctr: ctr.toFixed(1) + "%",
      pos: pos.toFixed(1),
      pain: Math.min(100, Math.round(opp))
    };
  }, [daily.rows]);

  const anyLoading = daily.loading || opportunities.loading || intents.loading || templates.loading;
  const anyError = daily.error || opportunities.error || intents.error || templates.error;

  if (anyError) {
    return <div style={{ padding: 24, color: COLORS.red, background: COLORS.bg }}>Query error: {String(anyError)}</div>;
  }

  return <main style={{ background: COLORS.bg, color: COLORS.text, minHeight: "100vh", padding: 28, fontFamily: "Inter, ui-sans-serif, system-ui" }}>
    <div style={{ display: "flex", justifyContent: "space-between", gap: 20, alignItems: "flex-start" }}>
      <div>
        <div style={{ display: "flex", gap: 8, marginBottom: 12 }}>
          <MiniBadge>public-safe GSC</MiniBadge><MiniBadge color={COLORS.blue}>no queries or URLs exposed</MiniBadge><MiniBadge color={COLORS.green}>normalized indexes</MiniBadge>
        </div>
        <h1 style={{ fontSize: 48, lineHeight: 1, margin: 0, letterSpacing: -1.5 }}>
          Anonymous Search Console<br/><span style={{ color: COLORS.duck }}>Opportunity Map</span>
        </h1>
        <p style={{ maxWidth: 780, color: COLORS.muted, fontSize: 16, lineHeight: 1.5 }}>
          A privacy-preserving SEO Dive: real Google Search Console performance, transformed so the public can see the story without seeing the domains, URLs, raw search queries, or traffic counts.
        </p>
      </div>
      <div style={{ display: "flex", gap: 8, background: COLORS.panel, border: `1px solid ${COLORS.grid}`, padding: 8, borderRadius: 999 }}>
        {["Site A", "Site B"].map(x => <button key={x} onClick={() => setSite(x)} style={{ cursor: "pointer", border: 0, borderRadius: 999, padding: "10px 16px", fontWeight: 800, background: site === x ? COLORS.duck : "transparent", color: site === x ? "#111" : COLORS.text }}>{x}</button>)}
      </div>
    </div>

    {anyLoading && <div style={{ color: COLORS.muted, marginTop: 12 }}>Loading live MotherDuck queries…</div>}

    <div style={{ display: "grid", gridTemplateColumns: "repeat(5, minmax(0, 1fr))", gap: 14, marginTop: 22 }}>
      <Card label="Demand now" value={kpis.demand} hint="latest normalized impression index" />
      <Card label="Clicks now" value={kpis.clicks} hint="latest normalized click index" color={COLORS.green} />
      <Card label="CTR drag" value={kpis.ctr} hint="average CTR across the window" color={COLORS.orange} />
      <Card label="Avg position" value={kpis.pos} hint="lower is better" color={COLORS.blue} />
      <Card label="Opportunity heat" value={kpis.pain} hint="100 = biggest unrealized demand" color={kpis.pain > 50 ? COLORS.red : COLORS.duck} />
    </div>

    <Panel title="The 90-day search pulse" subtitle="Demand and clicks are indexed, so the shape is real but business volume stays private.">
      <ResponsiveContainer width="100%" height={320}>
        <ComposedChart data={daily.rows}>
          <CartesianGrid stroke={COLORS.grid} />
          <XAxis dataKey="date" stroke={COLORS.muted} tick={{ fontSize: 11 }} />
          <YAxis stroke={COLORS.muted} />
          <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
          <Legend />
          <Area type="monotone" dataKey="impressions_index" name="Demand index" fill={COLORS.blue} stroke={COLORS.blue} fillOpacity={0.18} />
          <Line type="monotone" dataKey="clicks_index" name="Click index" stroke={COLORS.green} strokeWidth={3} dot={false} />
          <Line type="monotone" dataKey="opportunity_index" name="Opportunity heat" stroke={COLORS.orange} strokeWidth={2} dot={false} />
        </ComposedChart>
      </ResponsiveContainer>
    </Panel>

    <div style={{ display: "grid", gridTemplateColumns: "1.1fr .9fr", gap: 18 }}>
      <Panel title="Demand by intent" subtitle="Raw queries are classified, then removed. The public sees intent, not keywords.">
        <ResponsiveContainer width="100%" height={300}>
          <BarChart data={intents.rows} layout="vertical" margin={{ left: 24 }}>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis type="number" stroke={COLORS.muted} />
            <YAxis type="category" dataKey="intent_class" stroke={COLORS.muted} width={135} tick={{ fontSize: 12 }} />
            <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Bar dataKey="demand_index" name="Demand index" radius={[0, 8, 8, 0]}>
              {intents.rows.map((_, i) => <Cell key={i} fill={[COLORS.duck, COLORS.blue, COLORS.green, COLORS.orange, COLORS.purple][i % 5]} />)}
            </Bar>
          </BarChart>
        </ResponsiveContainer>
      </Panel>

      <Panel title="Page-template drag" subtitle="Where the site leaks demand: not page names, just page archetypes.">
        <ResponsiveContainer width="100%" height={300}>
          <ScatterChart>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis dataKey="avg_position" name="Avg position" stroke={COLORS.muted} />
            <YAxis dataKey="opportunity_index" name="Opportunity" stroke={COLORS.muted} />
            <Tooltip cursor={{ strokeDasharray: "3 3" }} contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Scatter data={templates.rows} fill={COLORS.duck} name="Templates" />
          </ScatterChart>
        </ResponsiveContainer>
        <div style={{ display: "grid", gridTemplateColumns: "repeat(2, 1fr)", gap: 8 }}>
          {templates.rows.map(t => <div key={t.page_template} style={{ background: COLORS.panel2, borderRadius: 12, padding: 10, color: COLORS.muted }}><b style={{ color: COLORS.text }}>{t.page_template}</b><br/>Opp: {t.opportunity_index} · CTR: {t.ctr_pct}%</div>)}
        </div>
      </Panel>
    </div>

    <Panel title="Top anonymous refresh candidates" subtitle="The highest-upside striking-distance queries. Query IDs are stable inside the dataset, but not reversible to the original search terms.">
      <div style={{ overflowX: "auto" }}>
        <table style={{ width: "100%", borderCollapse: "collapse", fontSize: 13 }}>
          <thead><tr style={{ color: COLORS.muted, textAlign: "left" }}>
            {['Query ID','Intent','Length','Demand','CTR','Position','Recommendation'].map(h => <th key={h} style={{ padding: 10, borderBottom: `1px solid ${COLORS.grid}` }}>{h}</th>)}
          </tr></thead>
          <tbody>
            {opportunities.rows.map((r, i) => <tr key={r.query_id} style={{ background: i % 2 ? COLORS.panel2 : "transparent" }}>
              <td style={{ padding: 10, color: COLORS.duck, fontWeight: 800 }}>{r.query_id}</td>
              <td style={{ padding: 10 }}>{r.intent_class}</td>
              <td style={{ padding: 10, color: COLORS.muted }}>{r.query_length_bucket}</td>
              <td style={{ padding: 10 }}>{r.impressions_index}</td>
              <td style={{ padding: 10 }}>{r.ctr_pct}%</td>
              <td style={{ padding: 10 }}>{r.avg_position}</td>
              <td style={{ padding: 10, color: COLORS.green }}>Refresh title, improve snippet, add internal links</td>
            </tr>)}
          </tbody>
        </table>
      </div>
    </Panel>

    <div style={{ display: "grid", gridTemplateColumns: "1fr 1fr", gap: 18 }}>
      <Panel title="Device x intent mismatch" subtitle="Where mobile/desktop behavior changes the SEO bet.">
        <ResponsiveContainer width="100%" height={280}>
          <BarChart data={device.rows.slice(0, 18)}>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis dataKey="device" stroke={COLORS.muted} />
            <YAxis stroke={COLORS.muted} />
            <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Bar dataKey="impressions_index" fill={COLORS.purple} name="Impressions index" />
          </BarChart>
        </ResponsiveContainer>
      </Panel>
      <Panel title="Region mix" subtitle="Countries are collapsed to coarse regions for privacy.">
        <ResponsiveContainer width="100%" height={280}>
          <BarChart data={regions.rows}>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis dataKey="region" stroke={COLORS.muted} tick={{ fontSize: 11 }} />
            <YAxis stroke={COLORS.muted} />
            <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Bar dataKey="impressions_index" fill={COLORS.blue} name="Demand index" />
            <Bar dataKey="clicks_index" fill={COLORS.green} name="Click index" />
          </BarChart>
        </ResponsiveContainer>
      </Panel>
    </div>

    <Panel title="Why this is public-safe" subtitle="The data story stays useful while private SEO assets stay private.">
      <div style={{ display: "grid", gridTemplateColumns: "repeat(4, 1fr)", gap: 12 }}>
        {["Domains removed", "URLs replaced with page IDs", "Queries replaced with intent + IDs", "Counts normalized to indexes"].map((x, i) => <div key={x} style={{ background: COLORS.panel2, borderRadius: 16, padding: 16, border: `1px solid ${COLORS.grid}` }}><div style={{ color: COLORS.duck, fontSize: 28, fontWeight: 900 }}>0{i + 1}</div><div style={{ fontWeight: 800 }}>{x}</div></div>)}
      </div>
    </Panel>
  </main>;
}


A privacy-preserving SEO Dive built from real Google Search Console data: domains, URLs, queries, and raw traffic counts are removed while intent, opportunity, and performance shape remain explorable.

Anonymous Search Console Opportunity Map

Jon Gouveia

An interactive, beginner-friendly DuckDB curriculum built around the classic Suppliers-Parts-Projects (SPJ) dataset. Lessons covering SELECT, filtering, aggregation, JOINs, and subqueries — with live query results throughout.

DuckDB Beginner's Guide — SPJ Dataset

gwdean

An interactive force-directed network of 327 Marvel characters and 9,891 co-appearance connections. Click nodes to explore characters, filter by team, find shortest paths between any two heroes, and discover wild Marvel facts.

The Marvel Social Network

Sekou Tyler


import { useState } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
  AreaChart, Area, XAxis, YAxis, Tooltip, ResponsiveContainer, CartesianGrid
} from "recharts";

export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/duckdb_stats/1eb684bf-faff-4860-8e7d-92af4ff9a410', alias: 'duckdb_stats' }
];


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

const C = {
  bg:        "#0f1117",
  surface:   "#1a1d27",
  border:    "#2a2d3a",
  text:      "#e8e8f0",
  muted:     "#6b6e82",
  primary:   "#0777b3",
  primaryDim:"#0a3d5c",
  amber:     "#f59e0b",
  green:     "#22c55e",
  purple:    "#a78bfa",
  positive:  "#22c55e",
  negative:  "#ef4444",
};

const fmt = (n) => {
  const v = N(n);
  if (v >= 1e9) return `${(v/1e9).toFixed(1)}B`;
  if (v >= 1e6) return `${(v/1e6).toFixed(1)}M`;
  if (v >= 1e3) return `${(v/1e3).toFixed(0)}K`;
  return v.toLocaleString();
};

const Tip = ({ active, payload, label }) => {
  if (!active || !payload?.length) return null;
  return (
    <div style={{background:C.surface,border:`1px solid ${C.border}`,padding:"8px 12px",borderRadius:6,fontSize:12}}>
      <div style={{color:C.muted,fontSize:10,marginBottom:3}}>{label}</div>
      <div style={{fontWeight:700,color:C.text,fontSize:14}}>{fmt(payload[0].value)}</div>
    </div>
  );
};

const HBar = ({ data, barColor }) => {
  const max = Math.max(...data.map(r => N(r.d)));
  return (
    <div style={{display:"flex",flexDirection:"column",gap:8}}>
      {data.map((row, i) => (
        <div key={row.k} style={{display:"flex",alignItems:"center",gap:8}}>
          <span style={{fontSize:11,color:i===0?C.text:C.muted,width:40,textAlign:"right",fontVariantNumeric:"tabular-nums",flexShrink:0}}>{row.k}</span>
          <div style={{flex:1,background:C.border,borderRadius:2,height:5,overflow:"hidden"}}>
            <div style={{background:i===0?barColor:C.primaryDim,width:`${(N(row.d)/max)*100}%`,height:"100%",borderRadius:2,opacity:i===0?1:0.7}}/>
          </div>
          <span style={{fontSize:10,color:C.muted,width:36,fontVariantNumeric:"tabular-nums",flexShrink:0}}>{fmt(row.d)}</span>
        </div>
      ))}
    </div>
  );
};

const Skeleton = ({ h = 16, w = "100%" }) => (
  <div style={{height:h,width:w,background:C.border,borderRadius:4,animation:"pulse 1.5s ease-in-out infinite"}}/>
);

const WEEK_ZOOMS = [
  {key:"all",  label:"All",   from:"2019-01-01"},
  {key:"2022", label:"2022+", from:"2022-01-01"},
  {key:"2024", label:"2024+", from:"2024-01-01"},
  {key:"6m",   label:"6M",    from:null},
];
const PERIODS = [
  {key:"all", label:"All time"},
  {key:"7",   label:"Last 7d"},
  {key:"30",  label:"Last 30d"},
  {key:"90",  label:"Last 90d"},
];

export default function PyPIDuckDBStats() {
  const [period, setPeriod] = useState("all");
  const [weekZoom, setWeekZoom] = useState("all");

  // Exclude current incomplete month — MoM/YoY should compare complete months only
  const monthlyQ = useSQLQuery(`
    SELECT strftime(date_trunc('month', download_date),'%Y-%m') as month,
           SUM(daily_download_sum) as downloads
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE date_trunc('month', download_date) < date_trunc('month', current_date)
      AND download_date >= (current_date - INTERVAL 14 MONTHS)
    GROUP BY 1 ORDER BY 1
  `);
  const monthlyRows = Array.isArray(monthlyQ.data) ? monthlyQ.data : [];
  const tableMonths = [...monthlyRows].reverse().slice(0, 6);
  const latestM  = monthlyRows[monthlyRows.length - 1];
  const prevM    = monthlyRows[monthlyRows.length - 2];
  const yoyM     = monthlyRows[0];
  const momPct   = latestM && prevM ? (N(latestM.downloads) - N(prevM.downloads)) / N(prevM.downloads) * 100 : null;
  const yoyPct   = latestM && yoyM  ? (N(latestM.downloads) - N(yoyM.downloads))  / N(yoyM.downloads)  * 100 : null;

  const kpiQ = useSQLQuery(`
    SELECT SUM(daily_download_sum) as total,
           strftime(MAX(download_date), '%b %d, %Y') as freshness
    FROM "duckdb_stats"."main"."pypi_daily_stats"
  `);
  const kpiRow = Array.isArray(kpiQ.data) && kpiQ.data.length > 0 ? kpiQ.data[0] : null;

  // Exclude current incomplete week
  const weeklyQ = useSQLQuery(`
    SELECT strftime(date_trunc('week', download_date),'%Y-%m-%d') as week,
           SUM(daily_download_sum) as downloads
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE date_trunc('week', download_date) < date_trunc('week', current_date)
    GROUP BY 1 ORDER BY 1
  `);
  const allWeekly = Array.isArray(weeklyQ.data) ? weeklyQ.data : [];

  // Coerce downloads to Number — Recharts cannot render BigInt values for chart heights
  const weekData = (() => {
    let raw;
    if (weekZoom === "6m") raw = allWeekly.slice(-26);
    else {
      const z = WEEK_ZOOMS.find(z => z.key === weekZoom);
      raw = z?.from ? allWeekly.filter(d => d.week >= z.from) : allWeekly;
    }
    return raw.map(d => ({ week: d.week, downloads: N(d.downloads) }));
  })();

  const weekTickFmt = (v) => {
    const d = new Date(v);
    if (weekZoom === "6m") return d.getDate() <= 7 ? d.toLocaleString("en",{month:"short"}) : "";
    return d.getMonth() === 0 && d.getDate() <= 7 ? String(d.getFullYear()) : "";
  };
  const weekTickInterval = weekZoom === "6m" ? 3 : weekZoom === "2024" ? 3 : weekZoom === "2022" ? 8 : 12;

  const periodFilter = period === "all" ? "" : `AND download_date >= current_date - INTERVAL ${period} DAYS`;

  const versionsQ = useSQLQuery(`
    SELECT regexp_extract(version, '^\\d+\\.\\d+') as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k != '' ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const pythonQ = useSQLQuery(`
    SELECT regexp_extract(python_version, '^\\d+\\.\\d+') as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k != '' ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const countryQ = useSQLQuery(`
    SELECT country_code as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k IS NOT NULL ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const osQ = useSQLQuery(`
    SELECT system_name as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k IS NOT NULL ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 6
  `);

  const versions  = Array.isArray(versionsQ.data) ? versionsQ.data : [];
  const python    = Array.isArray(pythonQ.data)   ? pythonQ.data   : [];
  const countries = Array.isArray(countryQ.data)  ? countryQ.data  : [];
  const os        = Array.isArray(osQ.data)        ? osQ.data       : [];
  const breakdownLoading = versionsQ.isLoading || pythonQ.isLoading || countryQ.isLoading || osQ.isLoading;

  return (
    <div style={{background:C.bg,minHeight:"100vh",color:C.text,fontFamily:"-apple-system,BlinkMacSystemFont,'Segoe UI',sans-serif"}}>
      <style>{`@keyframes pulse{0%,100%{opacity:1}50%{opacity:0.4}}`}</style>

      <div style={{padding:"22px 28px",maxWidth:1100,margin:"0 auto"}}>

        <div style={{marginBottom:20,display:"flex",alignItems:"center",gap:10}}>
          <div style={{width:8,height:8,borderRadius:"50%",background:C.primary,flexShrink:0}}/>
          <div>
            <span style={{fontSize:18,fontWeight:700}}>DuckDB</span>
            <span style={{fontSize:18,fontWeight:300,color:C.muted,marginLeft:6}}>· PyPI Download Stats</span>
          </div>
          <span style={{fontSize:11,color:C.muted,marginLeft:"auto"}}>2019 – present</span>
        </div>

        <div style={{display:"grid",gridTemplateColumns:"repeat(5,1fr)",gap:32,marginBottom:24}}>
          <div>
            {kpiQ.isLoading ? <Skeleton h={36} w="80%"/> : <div style={{fontSize:28,fontWeight:800,color:C.text,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums"}}>{kpiRow ? fmt(kpiRow.total) : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>all-time downloads</div>
          </div>
          <div>
            {monthlyQ.isLoading ? <Skeleton h={36} w="80%"/> : <div style={{fontSize:28,fontWeight:800,color:C.text,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums"}}>{latestM ? fmt(latestM.downloads) : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>{latestM ? `${latestM.month} downloads` : "latest month"}</div>
          </div>
          <div>
            {monthlyQ.isLoading ? <Skeleton h={36} w="60%"/> : <div style={{fontSize:28,fontWeight:800,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums",color:momPct!=null?(momPct>=0?C.positive:C.negative):C.muted}}>{momPct!=null ? `${momPct>0?"+":""}${momPct.toFixed(1)}%` : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>vs prior month</div>
          </div>
          <div>
            {monthlyQ.isLoading ? <Skeleton h={36} w="60%"/> : <div style={{fontSize:28,fontWeight:800,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums",color:yoyPct!=null?(yoyPct>=0?C.positive:C.negative):C.muted}}>{yoyPct!=null ? `${yoyPct>0?"+":""}${yoyPct.toFixed(0)}%` : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>year-over-year</div>
          </div>
          <div>
            {kpiQ.isLoading ? <Skeleton h={36} w="90%"/> : <div style={{fontSize:28,fontWeight:800,color:C.text,lineHeight:1.1,letterSpacing:"-0.5px"}}>{kpiRow?.freshness ?? "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>data freshness</div>
          </div>
        </div>

        <div style={{display:"grid",gridTemplateColumns:"240px 1fr",gap:20,marginBottom:20}}>
          <div style={{background:C.surface,borderRadius:10,padding:"14px 16px",border:`1px solid ${C.border}`,overflow:"hidden"}}>
            <div style={{fontSize:12,fontWeight:600,color:C.text,marginBottom:2}}>Monthly downloads</div>
            <div style={{fontSize:10,color:C.muted,marginBottom:12}}>last 6 complete months</div>
            {monthlyQ.isLoading
              ? <div style={{display:"flex",flexDirection:"column",gap:10}}>{[...Array(6)].map((_,i)=><Skeleton key={i} h={28}/>)}</div>
              : tableMonths.map((row, i) => {
                  const prev_ = tableMonths[i + 1];
                  const pct = prev_ ? ((N(row.downloads) - N(prev_.downloads)) / N(prev_.downloads) * 100) : null;
                  const isLatest = i === 0;
                  return (
                    <div key={row.month} style={{display:"flex",alignItems:"center",justifyContent:"space-between",padding:"5px 0",borderBottom:i<tableMonths.length-1?`1px solid ${C.border}`:"none"}}>
                      <span style={{fontSize:11,color:isLatest?C.text:C.muted,fontWeight:isLatest?600:400}}>{row.month}</span>
                      <div style={{textAlign:"right"}}>
                        <div style={{fontSize:isLatest?20:13,fontWeight:isLatest?800:500,color:isLatest?C.text:C.muted,lineHeight:1.2,fontVariantNumeric:"tabular-nums"}}>{fmt(row.downloads)}</div>
                        {pct!=null&&<div style={{fontSize:9,color:pct>=0?C.positive:C.negative,lineHeight:1.3}}>{pct>0?"▲":"▼"} {Math.abs(pct).toFixed(1)}%</div>}
                      </div>
                    </div>
                  );
                })
            }
          </div>

          <div style={{background:C.surface,borderRadius:10,padding:"14px 16px",border:`1px solid ${C.border}`}}>
            <div style={{display:"flex",alignItems:"center",justifyContent:"space-between",marginBottom:10}}>
              <div>
                <div style={{fontSize:12,fontWeight:600,color:C.text}}>Weekly downloads</div>
                <div style={{fontSize:10,color:C.muted,marginTop:2}}>May 2019 – present · complete weeks only</div>
              </div>
              <div style={{display:"flex",gap:3}}>
                {WEEK_ZOOMS.map(z=>(
                  <button key={z.key} onClick={()=>setWeekZoom(z.key)} style={{
                    padding:"3px 9px",fontSize:10,fontWeight:500,borderRadius:5,cursor:"pointer",
                    border:`1px solid ${weekZoom===z.key?C.primary:C.border}`,
                    background:weekZoom===z.key?C.primaryDim:"transparent",
                    color:weekZoom===z.key?C.primary:C.muted,
                  }}>{z.label}</button>
                ))}
              </div>
            </div>
            {weeklyQ.isLoading
              ? <div style={{background:C.border,borderRadius:6,height:305,animation:"pulse 1.5s ease-in-out infinite"}}/>
              : <ResponsiveContainer width="100%" height={305}>
                  <AreaChart data={weekData} margin={{top:4,right:4,left:0,bottom:0}}>
                    <defs>
                      <linearGradient id="g" x1="0" y1="0" x2="0" y2="1">
                        <stop offset="5%" stopColor={C.primary} stopOpacity={0.3}/>
                        <stop offset="95%" stopColor={C.primary} stopOpacity={0}/>
                      </linearGradient>
                    </defs>
                    <CartesianGrid strokeDasharray="3 3" stroke={C.border} vertical={false}/>
                    <XAxis dataKey="week" tick={{fontSize:10,fill:C.muted}} tickLine={false} axisLine={{stroke:C.border}}
                      tickFormatter={weekTickFmt} interval={weekTickInterval}/>
                    <YAxis tick={{fontSize:10,fill:C.muted}} tickLine={false} axisLine={false} tickFormatter={v=>fmt(v)} width={42}/>
                    <Tooltip content={<Tip/>}/>
                    <Area type="linear" dataKey="downloads" stroke={C.primary} strokeWidth={2} fill="url(#g)" dot={false} isAnimationActive={false}/>
                  </AreaChart>
                </ResponsiveContainer>
            }
          </div>
        </div>

        <div style={{background:C.surface,borderRadius:10,padding:"14px 16px",border:`1px solid ${C.border}`}}>
          <div style={{display:"flex",alignItems:"center",justifyContent:"space-between",marginBottom:16}}>
            <div>
              <div style={{fontSize:12,fontWeight:600,color:C.text}}>Download breakdown</div>
              <div style={{fontSize:10,color:C.muted,marginTop:2}}>by DuckDB version, Python version, country & OS</div>
            </div>
            <div style={{display:"flex",gap:4}}>
              {PERIODS.map(p=>(
                <button key={p.key} onClick={()=>setPeriod(p.key)} style={{
                  padding:"4px 11px",fontSize:11,fontWeight:500,borderRadius:6,cursor:"pointer",
                  border:`1px solid ${period===p.key?C.primary:C.border}`,
                  background:period===p.key?C.primaryDim:"transparent",
                  color:period===p.key?C.primary:C.muted,
                }}>{p.label}</button>
              ))}
            </div>
          </div>
          {breakdownLoading
            ? <div style={{display:"grid",gridTemplateColumns:"1fr 1fr 1fr 1fr",gap:24}}>
                {[...Array(4)].map((_,i)=>(
                  <div key={i} style={{display:"flex",flexDirection:"column",gap:8}}>
                    {[...Array(6)].map((__,j)=><Skeleton key={j} h={12}/>)}
                  </div>
                ))}
              </div>
            : <div style={{display:"grid",gridTemplateColumns:"1fr 1fr 1fr 1fr",gap:24}}>
                {[
                  {title:"DuckDB version", data:versions,  color:C.primary},
                  {title:"Python version", data:python,    color:C.amber},
                  {title:"Country",        data:countries, color:C.green},
                  {title:"OS",             data:os,        color:C.purple},
                ].map(({title,data,color})=>(
                  <div key={title}>
                    <div style={{fontSize:10,color:C.muted,marginBottom:10,fontWeight:600,textTransform:"uppercase",letterSpacing:"0.06em"}}>{title}</div>
                    {data.length > 0 ? <HBar data={data} barColor={color}/> : <div style={{fontSize:11,color:C.muted}}>No data</div>}
                  </div>
                ))}
              </div>
          }
        </div>

        <div style={{marginTop:14,fontSize:10,color:C.border,textAlign:"right"}}>
          duckdb_stats.main.pypi_daily_stats · updated daily
        </div>
      </div>
    </div>
  );
}


Weekly and monthly DuckDB download trends on PyPI since 2019, with version, Python, country and OS breakdowns. Sourced from duckdb_stats.main.pypi_daily_stats.

DuckDB PyPI Download Stats

Mehdi Ouazza

Three-plus years of Anthropic's public status-page incidents on live MotherDuck data. Interactive: KPIs, monthly outages by severity (count / incident-hours / recovery time), longest incidents linked to their status posts, your-timezone workday overlap, and a real-timezone world map shaded by how often outages land in the local workday.

Dive Into Claude Outages

Will Hea

A static code atlas of module activity and co-change across the imported DuckDB repository history.

DuckDB Code Atlas

Sahil Gupta

An interactive dashboard tracking the career evolution of Nikola Jokić, highlighting his scoring growth, playmaking, and triple-double frequency.

Nikola Jokić Career Arc

Jacob Matson

A Minard-style flow map of Chinook salmon migration through the Columbia and Snake River basin. Styled as a 19th-century military campaign map, the Fleet of the Columbia traces its passage from the Pacific Ocean through eight dam fortifications to the spawning grounds. Animated with a passage log recording each encounter, fish population data for 1938, 1960, and 2023.

The Salmon Run — Columbia Basin Migration Atlas

Create a Minard-style flow map of Chinook salmon migration through the Columbia and Snake River Basin as a MotherDuck Dive. Style it as a 19th-century military campaign map — parchment background, engraved serif fonts, crosshatch terrain, mountain symbols, double-ruled rivers. The band width represents fish population, narrowing at each of the 8 major dams to show mortality. Show three years: 1938, 1960, and 2023. Animate the salmon's journey from the Pacific to the spawning grounds, pausing at each dam. Include a sidebar log that fills in as each dam is encountered, recording the date, fish count, and losses. Call it The Fleet of the Columbia.

Make the animation smooth and fluid, the band geometrically accurate to Minard's style with sharp instantaneous width changes at each dam rather than gradual tapers, and the overall aesthetic as close to a genuine 19th-century engraved campaign map as possible. Give the salmon band a fish tail at the start and a wedge head at the front. Add period typography, a compass rose, tide-mark arcs at the ocean entry, geographic labels, and a field journal styled sidebar that records each dam encounter with arrival dates and casualties.

Mehrab Rahman

import { useState, useMemo } from "react";
import * as d3 from "d3";
import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query";


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/my_db/272d16fb-2b68-42d6-88f3-faad5869cb54', alias: 'my_db' }
];

const N = (v) => (v != null ? Number(v) : 0);
const W = 1000, H = 640, PAD = 46;
const NOUNS = ["CLUSTER", "BELT", "CLOUD", "CHAIN", "FIELD", "STREAM", "REACH", "VEIL", "DRIFT", "EXPANSE"];

export default function FlavorConstellations() {
  const { data, isLoading, isError, error } = useSQLQuery(`
    SELECT ingredient, x, y, category, compound_count
    FROM "my_db"."main"."divemaxxing_flavor_universe"
  `);

  const [selectedCategory, setSelectedCategory] = useDiveState("category", "");
  const [showBridges, setShowBridges] = useDiveState("bridges", false);
  const [search, setSearch] = useState("");
  const [hover, setHover] = useState(null);

  const points = useMemo(() => {
    const rows = Array.isArray(data) ? data : [];
    return rows.map((r) => ({
      ingredient: r.ingredient, x: N(r.x), y: N(r.y),
      category: r.category, compound_count: N(r.compound_count),
    }));
  }, [data]);

  const categories = useMemo(() => {
    const counts = {};
    points.forEach((p) => { counts[p.category] = (counts[p.category] || 0) + 1; });
    return Object.entries(counts).sort((a, b) => b[1] - a[1]);
  }, [points]);

  const colorMap = useMemo(() => {
    const m = {}; const n = categories.length || 1;
    categories.forEach(([cat], i) => { m[cat] = d3.interpolateSinebow(i / n); });
    return m;
  }, [categories]);
  const brightMap = useMemo(() => {
    const m = {};
    Object.entries(colorMap).forEach(([c, hex]) => {
      const col = d3.color(hex); m[c] = col ? col.brighter(0.9).formatHex() : hex;
    });
    return m;
  }, [colorMap]);
  const nameMap = useMemo(() => {
    const m = {};
    categories.forEach(([cat], i) => { m[cat] = "THE " + cat.toUpperCase() + " " + NOUNS[i % NOUNS.length]; });
    return m;
  }, [categories]);

  const project = useMemo(() => {
    if (!points.length) return null;
    const xs = points.map((p) => p.x), ys = points.map((p) => p.y);
    const sx = d3.scaleLinear().domain([Math.min(...xs), Math.max(...xs)]).range([PAD, W - PAD]);
    const sy = d3.scaleLinear().domain([Math.min(...ys), Math.max(...ys)]).range([H - PAD, PAD]);
    return { sx, sy };
  }, [points]);

  const screen = useMemo(() => {
    if (!project) return [];
    return points.map((p) => ({ ...p, X: project.sx(p.x), Y: project.sy(p.y) }));
  }, [points, project]);

  const sizeScale = useMemo(() => {
    const vals = points.map((p) => p.compound_count);
    return d3.scaleSqrt().domain([Math.min(...vals) || 0, Math.max(...vals) || 1]).range([1.7, 6.2]);
  }, [points]);

  const twinkleSet = useMemo(() => {
    if (!points.length) return new Set();
    const sorted = [...points].sort((a, b) => b.compound_count - a.compound_count);
    return new Set(sorted.slice(0, Math.ceil(points.length * 0.16)).map((p) => p.ingredient));
  }, [points]);

  // Constellation edges: each star links to its 2 nearest same-family neighbors.
  const edges = useMemo(() => {
    const byCat = {};
    screen.forEach((p) => { (byCat[p.category] = byCat[p.category] || []).push(p); });
    const seen = new Set(); const out = [];
    Object.values(byCat).forEach((group) => {
      group.forEach((p) => {
        group.filter((o) => o !== p)
          .map((o) => ({ o, d: (p.X - o.X) ** 2 + (p.Y - o.Y) ** 2 }))
          .sort((a, b) => a.d - b.d).slice(0, 2)
          .forEach(({ o }) => {
            const key = [p.ingredient, o.ingredient].sort().join("|");
            if (!seen.has(key)) { seen.add(key); out.push({ key, x1: p.X, y1: p.Y, x2: o.X, y2: o.Y, category: p.category }); }
          });
      });
    });
    return out;
  }, [screen]);

  // Bridges: stars sitting closer to a foreign family than their own kin.
  const bridges = useMemo(() => {
    if (screen.length < 3) return { set: new Map(), list: [] };
    const scored = screen.map((p, i) => {
      let dSame = Infinity, dDiff = Infinity, diffCat = null, fx = null, fy = null;
      for (let j = 0; j < screen.length; j++) {
        if (j === i) continue; const o = screen[j];
        const d = (p.x - o.x) ** 2 + (p.y - o.y) ** 2;
        if (o.category === p.category) { if (d < dSame) dSame = d; }
        else if (d < dDiff) { dDiff = d; diffCat = o.category; fx = o.X; fy = o.Y; }
      }
      const ratio = dDiff > 0 ? dSame / dDiff : 0;
      return { ingredient: p.ingredient, category: p.category, X: p.X, Y: p.Y, diffCat, fx, fy, ratio, dSame, dDiff };
    });
    const list = scored.filter((s) => isFinite(s.dSame) && isFinite(s.dDiff) && s.diffCat)
      .sort((a, b) => b.ratio - a.ratio).slice(0, 12);
    return { set: new Map(list.map((b) => [b.ingredient, b])), list };
  }, [screen]);

  const labels = useMemo(() => {
    const top = categories.filter(([, n]) => n >= 14).map(([c]) => c);
    return top.map((cat) => {
      const g = screen.filter((p) => p.category === cat);
      if (!g.length) return null;
      return { cat, X: g.reduce((s, p) => s + p.X, 0) / g.length, Y: g.reduce((s, p) => s + p.Y, 0) / g.length };
    }).filter(Boolean);
  }, [screen, categories]);

  const dust = useMemo(() => {
    let s = 42; const rnd = () => { s = (s * 1103515245 + 12345) & 0x7fffffff; return s / 0x7fffffff; };
    return Array.from({ length: 150 }, () => ({ x: rnd() * W, y: rnd() * H, r: rnd() * 0.9 + 0.2, o: rnd() * 0.5 + 0.12 }));
  }, []);

  const q = search.trim().toLowerCase();
  const anyFilter = showBridges || !!q || !!selectedCategory;
  const isActive = (p) => {
    if (showBridges) return bridges.set.has(p.ingredient);
    if (q) return p.ingredient.toLowerCase().includes(q);
    if (selectedCategory) return p.category === selectedCategory;
    return true;
  };
  const dimOf = (p) => (anyFilter ? (isActive(p) ? 1 : 0.1) : 1);
  const edgeOpacity = (e) => {
    if (showBridges) return 0.04;
    if (selectedCategory) return e.category === selectedCategory ? 0.6 : 0.035;
    if (q) return 0.06;
    return 0.22;
  };

  const clampX = (x) => Math.max(8, Math.min(W - 130, x));

  return (
    <div style={{ background: "#05070d", color: "#e6edf3", padding: "20px 22px", fontFamily: "Georgia, 'Times New Roman', serif" }}>
      <style>{`@keyframes tw{0%,100%{opacity:1}50%{opacity:.4}}@keyframes dash{to{stroke-dashoffset:-24}}@keyframes pulseb{0%,100%{box-shadow:0 0 0 0 rgba(202,162,58,0)}50%{box-shadow:0 0 0 4px rgba(202,162,58,.20)}}`}</style>

      <h1 style={{ fontSize: 27, fontWeight: 700, margin: "0 0 2px", letterSpacing: "-0.01em" }}>The Flavor Universe</h1>
      <p style={{ margin: "0 0 14px", color: "#8b949e", fontSize: 14, fontFamily: "system-ui, sans-serif" }}>
        450 ingredients as a night sky — stars are ingredients, constellations are flavor families
        {hover ? <span style={{ color: "#e6edf3" }}> · <b>{hover.ingredient}</b> <span style={{ color: colorMap[hover.category] }}>●</span> {hover.category}, {N(hover.compound_count)} compounds</span> : null}
      </p>

      <div style={{ display: "flex", gap: 10, alignItems: "center", marginBottom: 12, flexWrap: "wrap", fontFamily: "system-ui,sans-serif" }}>
        <input value={search} onChange={(e) => setSearch(e.target.value)} placeholder="Search an ingredient…"
          style={{ background: "#10141d", border: "1px solid #2a3140", borderRadius: 6, padding: "7px 11px", color: "#e6edf3", fontSize: 13, width: 210, outline: "none" }} />
        <button onClick={() => setShowBridges(!showBridges)}
          style={{ background: showBridges ? "#caa23a" : "transparent", border: "1px solid " + (showBridges ? "#caa23a" : "#4a4326"), color: showBridges ? "#1a1300" : "#e3c977", borderRadius: 6, padding: "7px 13px", fontSize: 13, cursor: "pointer", fontWeight: 600, animation: showBridges ? undefined : "pulseb 2.4s ease-in-out infinite" }}>
          {showBridges ? "✦ Bridges on" : "✦ Show the bridges"}
        </button>
        {selectedCategory && !showBridges ? (
          <button onClick={() => setSelectedCategory("")} style={{ background: "transparent", border: "1px solid #2a3140", color: "#8b949e", borderRadius: 6, padding: "6px 10px", fontSize: 12, cursor: "pointer" }}>
            ✕ {selectedCategory}
          </button>
        ) : null}
      </div>

      {isError ? (
        <div style={{ color: "#ff7b72", padding: 20 }}>Failed to load data: {String(error)}</div>
      ) : isLoading ? (
        <div className="animate-pulse" style={{ height: 460, background: "#0c1018", borderRadius: 10 }} />
      ) : (
        <div style={{ borderRadius: 10, overflow: "hidden", border: "1px solid #1b2230" }}>
          <svg viewBox={`0 0 ${W} ${H}`} width="100%" style={{ display: "block" }} preserveAspectRatio="xMidYMid meet">
            <defs>
              <radialGradient id="space" cx="38%" cy="32%" r="85%">
                <stop offset="0%" stopColor="#111a2e" />
                <stop offset="55%" stopColor="#080b15" />
                <stop offset="100%" stopColor="#04060c" />
              </radialGradient>
              <radialGradient id="vig" cx="50%" cy="47%" r="72%">
                <stop offset="58%" stopColor="#000000" stopOpacity="0" />
                <stop offset="100%" stopColor="#000000" stopOpacity="0.55" />
              </radialGradient>
            </defs>
            <rect x="0" y="0" width={W} height={H} fill="url(#space)" />
            {dust.map((d, i) => (
              <circle key={"d" + i} cx={d.x} cy={d.y} r={d.r} fill="#cdd6e6" opacity={d.o} />
            ))}

            {/* constellation lines */}
            {edges.map((e) => (
              <line key={e.key} x1={e.x1} y1={e.y1} x2={e.x2} y2={e.y2}
                stroke={colorMap[e.category]} strokeWidth={0.6} opacity={edgeOpacity(e)} />
            ))}

            {/* bridge comets */}
            {showBridges && bridges.list.map((b) => (
              <line key={"b" + b.ingredient} x1={b.X} y1={b.Y} x2={b.fx} y2={b.fy}
                stroke="#ffe08a" strokeWidth={1.1} opacity={0.85}
                strokeDasharray="3 4" style={{ animation: "dash 1.1s linear infinite" }} />
            ))}

            {/* stars */}
            {screen.map((p) => {
              const r = sizeScale(p.compound_count);
              const dim = dimOf(p);
              const isBridge = showBridges && bridges.set.has(p.ingredient);
              const isHover = hover && hover.ingredient === p.ingredient;
              const tw = twinkleSet.has(p.ingredient) && !anyFilter;
              return (
                <g key={p.ingredient} style={{ cursor: "pointer" }}
                  onMouseEnter={() => setHover(p)} onMouseLeave={() => setHover(null)}>
                  <circle cx={p.X} cy={p.Y} r={r * 2.4} fill={colorMap[p.category]} opacity={0.16 * dim}
                    style={tw ? { animation: `tw ${2.4 + (r % 1.6)}s ease-in-out infinite` } : undefined} />
                  {r > 3.6 ? <circle cx={p.X} cy={p.Y} r={r * 1.5} fill={brightMap[p.category]} opacity={0.12 * dim} /> : null}
                  <circle cx={p.X} cy={p.Y} r={isHover ? r + 1.6 : r} fill={brightMap[p.category]}
                    opacity={Math.min(1, 0.92 * dim + (isHover ? 0.2 : 0))}
                    stroke={isHover || isBridge ? "#ffffff" : "none"} strokeWidth={isHover || isBridge ? 1.2 : 0} />
                  {r > 4.4 && dim > 0.5 ? <circle cx={p.X} cy={p.Y} r={1} fill="#ffffff" opacity={0.9 * dim} /> : null}
                </g>
              );
            })}

            {/* cinematic vignette */}
            <rect x="0" y="0" width={W} height={H} fill="url(#vig)" style={{ pointerEvents: "none" }} />

            {/* bridge labels */}
            {showBridges && bridges.list.map((b) => (
              <text key={"bl" + b.ingredient} x={clampX(b.X + 7)} y={b.Y + 3.5} fontSize={10.5}
                fontFamily="system-ui, sans-serif" fill="#f6e7b8" style={{ paintOrder: "stroke", pointerEvents: "none" }}
                stroke="#05070d" strokeWidth={3} strokeLinejoin="round">
                {b.ingredient}<tspan fill="#9a8f6a"> → {b.diffCat}</tspan>
              </text>
            ))}

            {/* constellation names */}
            {!showBridges && labels.map((l) => (
              <text key={"l" + l.cat} x={l.X} y={l.Y} textAnchor="middle" fontSize={11}
                fontFamily="system-ui, sans-serif" fill="#e6edf3"
                opacity={selectedCategory ? (l.cat === selectedCategory ? 0.95 : 0.14) : 0.6}
                letterSpacing="2" style={{ pointerEvents: "none", paintOrder: "stroke" }}
                stroke="#05070d" strokeWidth={2.6} strokeLinejoin="round">
                {nameMap[l.cat]}
              </text>
            ))}

            {/* hover name */}
            {hover ? (
              <text x={clampX(hover.X + 8)} y={hover.Y - 8} fontSize={12} fontFamily="system-ui, sans-serif"
                fill="#ffffff" style={{ paintOrder: "stroke", pointerEvents: "none" }}
                stroke="#05070d" strokeWidth={3.5} strokeLinejoin="round">
                {hover.ingredient}
              </text>
            ) : null}

            {/* signature */}
            <text x={W - 14} y={H - 14} textAnchor="end" fontSize={11} fontFamily="system-ui, sans-serif"
              fill="#6a7585" opacity={0.85} letterSpacing="0.5" style={{ pointerEvents: "none" }}>
              ✦ compkitchen.com
            </text>
          </svg>
        </div>
      )}

      {/* clickable family legend */}
      {!showBridges ? (
        <div style={{ marginTop: 12, fontFamily: "system-ui,sans-serif" }}>
          <div style={{ display: "flex", flexWrap: "wrap", gap: 6, maxHeight: 80, overflowY: "auto", paddingRight: 4 }}>
            {categories.map(([cat, n]) => {
              const active = selectedCategory === cat;
              return (
                <button key={cat} onClick={() => setSelectedCategory(active ? "" : cat)}
                  style={{ display: "flex", alignItems: "center", gap: 5, background: active ? "#161c28" : "transparent",
                    border: "1px solid " + (active ? colorMap[cat] : "#2a3140"), borderRadius: 20, padding: "3px 9px",
                    cursor: "pointer", color: "#c9d1d9", fontSize: 11.5, lineHeight: 1.4,
                    opacity: selectedCategory && !active ? 0.4 : 1 }}>
                  <span style={{ width: 9, height: 9, borderRadius: "50%", background: colorMap[cat], display: "inline-block" }} />
                  {cat} <span style={{ color: "#6e7681" }}>{n}</span>
                </button>
              );
            })}
          </div>
        </div>
      ) : null}

      <p style={{ marginTop: 14, color: showBridges ? "#d8cfa8" : "#8b949e", fontSize: 13, lineHeight: 1.5, maxWidth: 780, fontFamily: "system-ui, sans-serif" }}>
        {showBridges
          ? `${bridges.list.length} stars sit closer to another constellation than to their own — ingredients chemically drifting between flavor families. These cross-category bridges are the surprising pairings molecular gastronomy chases.`
          : "Each star is an ingredient; brightness is the number of aroma compounds it carries. Lines trace each flavor family into a constellation. ✦ Hit “Show the bridges” to reveal the ingredients defecting to other families."}
      </p>
    </div>
  );
}

The Flavor Universe maps 450 ingredients as a night sky. Each star is an ingredient, its brightness the number of aroma compounds it carries; each flavor family is traced into its own constellation. Positions come from a 2D UMAP of the volatile compounds ingredients share, so proximity = molecular kinship. Hit "Show the bridges" to reveal the ingredients sitting closer to a foreign family than their own — the cross-category pairings molecular gastronomy chases. Built on peer-reviewed volatile-compound data through CompKitchen's flavor-pairing engine. → compkitchen.com

The Flavor Universe (Constellation): 450 Ingredients by Molecular Similarity

Build an interactive star map from my flavor_universe table — 450 ingredients placed by 2D UMAP coordinates of the aroma compounds they share, so proximity = molecular similarity. Stars sized/brightened by compound_count, colored by category, with each flavor family traced into a constellation. Dark editorial night-sky background.

Add a derived "bridges" metric: for each ingredient, find its nearest neighbor in a different flavor family vs. its own, and surface the dozen that sit closer to a foreign family — drawn as comet-streaks with a "Show the bridges" toggle. These are the cross-category pairings.

Polish it: name each constellation like a star atlas, add a vignette and extra glow on the brightest stars, make the bridges toggle pulse, and sign it compkitchen.com in the corner.

Dive Gallery