Skip to content

Instantly share code, notes, and snippets.

@matsonj

matsonj/attach_db.sql

Last active February 25, 2026 16:15
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save matsonj/781aca0a3e1b889059b4687feb1417bb to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save matsonj/781aca0a3e1b889059b4687feb1417bb to your computer and use it in GitHub Desktop.
Download ZIP
NBA Game Quality Explorer
Raw
attach_db.sql
ATTACH 'md:_share/nba_box_scores/c9126ae3-ef30-4b6e-af8e-d2212c1f2797';
Raw
create_nba_dive.sql
SELECT * FROM MD_CREATE_DIVE(
title = 'NBA Game Quality Explorer — 2025-26 v2',
content = $abcd$
import { useState, useMemo, useRef, useEffect } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";
export const REQUIRED_DATABASES = [
{ type: 'share', path: 'md:_share/nba_box_scores/c9126ae3-ef30-4b6e-af8e-d2212c1f2797', alias: 'nba_box_scores' }
];
const N = (v) => (v != null ? Number(v) : 0);
function isoWeek(y, m, d) {
const dt = new Date(Date.UTC(y, m - 1, d));
dt.setUTCDate(dt.getUTCDate() + 4 - (dt.getUTCDay() || 7));
const y1 = new Date(Date.UTC(dt.getUTCFullYear(), 0, 1));
return Math.ceil(((dt - y1) / 86400000 + 1) / 7);
}
const PAGE_SIZE = 50;
const STATS = ["points", "rebounds", "assists", "steals", "blocks", "turnovers", "fg3_made", "fg_v", "ft_v", "game_quality"];
const STAT_LABELS = { points: "PTS", rebounds: "REB", assists: "AST", steals: "STL", blocks: "BLK", turnovers: "TO", fg3_made: "3PTM", fg_v: "FG_V", ft_v: "FT_V", game_quality: "GQ" };
const HEATMAP_BINS = 20;
const HEATMAP_COLORS = ["#f5f5f5","#e8edf4","#dce4f2","#cfdcf0","#c2d3ee","#b4caec","#a6c0e9","#97b6e6","#88ace3","#79a1df","#6a96db","#5b8bd7","#4d80d3","#4075cf","#356ac8"];
/* ── Filter parsing (Datadog-style key:value) ────────────────────── */
const FILTER_KEYS = {
// String filters
team: { field: 'team_id', match: 'contains' },
player: { field: 'player_name', match: 'contains' },
opp: { field: 'opponent', match: 'contains' },
opponent: { field: 'opponent', match: 'contains' },
date: { field: 'game_date_short', match: 'contains' },
// Numeric filters
pts: { field: 'points', match: 'numeric' },
points: { field: 'points', match: 'numeric' },
reb: { field: 'rebounds', match: 'numeric' },
rebounds: { field: 'rebounds', match: 'numeric' },
ast: { field: 'assists', match: 'numeric' },
assists: { field: 'assists', match: 'numeric' },
stl: { field: 'steals', match: 'numeric' },
steals: { field: 'steals', match: 'numeric' },
blk: { field: 'blocks', match: 'numeric' },
blocks: { field: 'blocks', match: 'numeric' },
to: { field: 'turnovers', match: 'numeric' },
turnovers: { field: 'turnovers', match: 'numeric' },
'3ptm': { field: 'fg3_made', match: 'numeric' },
min: { field: 'mins', match: 'numeric' },
mins: { field: 'mins', match: 'numeric' },
gq: { field: 'game_quality', match: 'numeric' },
fg_v: { field: 'fg_v', match: 'numeric' },
ft_v: { field: 'ft_v', match: 'numeric' },
'fg%': { field: 'fg_pct', match: 'numeric' },
fg_pct: { field: 'fg_pct', match: 'numeric' },
'ft%': { field: 'ft_pct', match: 'numeric' },
ft_pct: { field: 'ft_pct', match: 'numeric' },
// Date part filters
year: { field: 'year', match: 'numeric' },
month: { field: 'month', match: 'numeric' },
day: { field: 'day', match: 'numeric' },
week: { field: 'week', match: 'numeric' },
};
function parseNumericValue(value) {
const m = value.match(/^(>=|<=|>|<|=)?(.+)$/);
if (!m) return null;
const op = m[1] || '=';
const num = parseFloat(m[2]);
if (isNaN(num)) return null;
return { op, num };
}
function parseFilters(text) {
const filters = [];
const regex = /([\w%]+):(?:"([^"]*)"|([\S]*))/g;
let match;
while ((match = regex.exec(text)) !== null) {
const key = match[1].toLowerCase();
const value = match[2] !== undefined ? match[2] : match[3];
if (value) filters.push({ key, value });
}
return filters;
}
function parseList(value) {
if (value.startsWith('[') && value.endsWith(']'))
return value.slice(1, -1).split(',').map(s => s.trim()).filter(Boolean);
return null;
}
function applyFilters(data, filters) {
if (filters.length === 0) return data;
return data.filter(row =>
filters.every(({ key, value }) => {
const config = FILTER_KEYS[key];
if (!config) return true;
const list = parseList(value);
if (config.match === 'numeric') {
if (list) {
const nums = list.map(Number).filter(n => !isNaN(n));
return nums.includes(Number(row[config.field]));
}
const parsed = parseNumericValue(value);
if (!parsed) return true;
const n = Number(row[config.field]);
switch (parsed.op) {
case '>': return n > parsed.num;
case '>=': return n >= parsed.num;
case '<': return n < parsed.num;
case '<=': return n <= parsed.num;
default: return n === parsed.num;
}
}
const cell = String(row[config.field] ?? '').toLowerCase();
if (list) {
const items = list.map(s => s.toLowerCase());
return config.match === 'contains'
? items.some(v => cell.includes(v))
: items.includes(cell);
}
const v = value.toLowerCase();
return config.match === 'contains' ? cell.includes(v) : cell === v;
})
);
}
function filtersToSQL(filters) {
const dataOnly = filters.filter(f => f.key !== 'heatmap' && f.key !== 'dots' && f.key !== 'top');
const topFilter = filters.find(f => f.key === 'top');
const topN = topFilter ? parseInt(topFilter.value, 10) : 0;
const parts = [];
if (dataOnly.length > 0) parts.push('WHERE ' + dataOnly.map(({ key, value }) => {
const config = FILTER_KEYS[key];
if (!config) return `/* unknown: ${key} */`;
const list = parseList(value);
if (list) {
if (config.match === 'numeric')
return `${config.field} IN (${list.join(', ')})`;
if (config.match === 'contains')
return '(' + list.map(v => `${config.field} ILIKE '%${v}%'`).join(' OR ') + ')';
return `${config.field} IN (${list.map(v => `'${v}'`).join(', ')})`;
}
if (config.match === 'numeric') {
const parsed = parseNumericValue(value);
if (!parsed) return `/* invalid: ${key}:${value} */`;
return `${config.field} ${parsed.op} ${parsed.num}`;
}
return config.match === 'contains'
? `${config.field} ILIKE '%${value}%'`
: `${config.field} = '${value}'`;
}).join('\n AND '));
if (topN > 0) parts.push(`LIMIT ${topN}`);
return parts.length > 0 ? parts.join('\n') : '-- no active filters';
}
/* ── Heatmap helpers ─────────────────────────────────────────────── */
function buildHeatmap(data, xKey, yKey, bounds) {
const xMin = bounds.xMin, xMax = bounds.xMax, yMin = bounds.yMin, yMax = bounds.yMax;
const xRange = xMax - xMin || 1, yRange = yMax - yMin || 1;
const xStep = xRange / HEATMAP_BINS, yStep = yRange / HEATMAP_BINS;
if (data.length === 0) return { bins: [], xTicks: [], yTicks: [], maxCount: 0, xMin, xMax, yMin, yMax, xStep, yStep };
const grid = {}; let maxCount = 0;
for (const d of data) {
const xi = Math.min(HEATMAP_BINS - 1, Math.max(0, Math.floor((d[xKey] - xMin) / xStep)));
const yi = Math.min(HEATMAP_BINS - 1, Math.max(0, Math.floor((d[yKey] - yMin) / yStep)));
const key = `${xi},${yi}`; grid[key] = (grid[key] || 0) + 1;
if (grid[key] > maxCount) maxCount = grid[key];
}
const bins = Object.entries(grid).map(([key, count]) => { const [xi, yi] = key.split(",").map(Number); return { xi, yi, count }; });
const fmt = (k, v) => k === "game_quality" ? v.toFixed(3) : v.toFixed(1);
return { bins, xTicks: Array.from({length:5},(_,i)=>fmt(xKey,xMin+(xRange*i)/4)), yTicks: Array.from({length:5},(_,i)=>fmt(yKey,yMin+(yRange*i)/4)), maxCount, xMin, xMax, yMin, yMax, xStep, yStep };
}
function buildPlayerDots(hlData, xKey, yKey, hm, cellW, cellH, padLeft, padTop) {
if (!hlData || hlData.length === 0 || !hm.xStep) return [];
const hash = (i) => {
let x = Math.sin(i * 127.1 + 311.7) * 43758.5453;
return x - Math.floor(x);
};
const r = Math.min(cellW, cellH) * 0.14 + 2;
const dots = [];
for (let i = 0; i < hlData.length; i++) {
const d = hlData[i];
const xi = Math.min(HEATMAP_BINS-1, Math.floor((d[xKey]-hm.xMin)/hm.xStep));
const yi = Math.min(HEATMAP_BINS-1, Math.floor((d[yKey]-hm.yMin)/hm.yStep));
const baseCx = padLeft + xi * cellW + cellW / 2;
const baseCy = padTop + (HEATMAP_BINS - 1 - yi) * cellH + cellH / 2;
const jitterX = (hash(i * 2) - 0.5) * (cellW * 0.7);
const jitterY = (hash(i * 2 + 1) - 0.5) * (cellH * 0.7);
dots.push({ cx: baseCx + jitterX, cy: baseCy + jitterY, r });
}
return dots;
}
/* ── Heatmap (clickable axis labels) ─────────────────────────────── */
function Heatmap({ data, xKey, yKey, hlData, selectedBin, onBinClick, onXLabelClick, onYLabelClick, bounds, width=620, height=280 }) {
const pad={top:16,right:16,bottom:48,left:46};
const w=width-pad.left-pad.right, h=height-pad.top-pad.bottom;
const hm=useMemo(()=>buildHeatmap(data,xKey,yKey,bounds),[data,xKey,yKey,bounds]);
const cellW=w/HEATMAP_BINS, cellH=h/HEATMAP_BINS;
const [hover,setHover]=useState(null);
const playerDots=useMemo(()=>buildPlayerDots(hlData,xKey,yKey,hm,cellW,cellH,pad.left,pad.top),[hlData,xKey,yKey,hm,cellW,cellH]);
// Contour: draw edges where filled cells border empty cells
const contourPath=useMemo(()=>{
if(hm.bins.length===0) return '';
const filled=new Set(hm.bins.map(b=>`${b.xi},${b.yi}`));
const segs=[];
for(const b of hm.bins){
const sx=pad.left+b.xi*cellW;
const sy=pad.top+(HEATMAP_BINS-1-b.yi)*cellH;
if(!filled.has(`${b.xi-1},${b.yi}`)) segs.push(`M${sx},${sy}V${sy+cellH}`);
if(!filled.has(`${b.xi+1},${b.yi}`)) segs.push(`M${sx+cellW},${sy}V${sy+cellH}`);
if(!filled.has(`${b.xi},${b.yi+1}`)) segs.push(`M${sx},${sy}H${sx+cellW}`);
if(!filled.has(`${b.xi},${b.yi-1}`)) segs.push(`M${sx},${sy+cellH}H${sx+cellW}`);
}
return segs.join('');
},[hm.bins,cellW,cellH,pad.left,pad.top]);
if(data.length===0) return null;
return (
<svg width={width} height={height} style={{display:"block",maxWidth:"100%"}}>
{hm.bins.map(b=>{
const ci=Math.min(HEATMAP_COLORS.length-1,Math.floor((b.count/hm.maxCount)*(HEATMAP_COLORS.length-1)));
const isSel=selectedBin&&selectedBin.xi===b.xi&&selectedBin.yi===b.yi;
return <rect key={`${b.xi}-${b.yi}`} x={pad.left+b.xi*cellW} y={pad.top+(HEATMAP_BINS-1-b.yi)*cellH}
width={cellW+0.5} height={cellH+0.5} fill={HEATMAP_COLORS[ci]}
stroke={isSel?"#231f20":"none"} strokeWidth={isSel?2:0} style={{cursor:"pointer"}}
onClick={()=>onBinClick(b.xi,b.yi,hm)}
onMouseEnter={()=>setHover({count:b.count,px:pad.left+b.xi*cellW+cellW/2,py:pad.top+(HEATMAP_BINS-1-b.yi)*cellH})}
onMouseLeave={()=>setHover(null)} />;
})}
{contourPath && <path d={contourPath} stroke="#bbb" strokeWidth={0.75} fill="none" style={{pointerEvents:"none"}} />}
{playerDots.map((dot,i)=>(
<circle key={`pd-${i}`} cx={dot.cx} cy={dot.cy} r={dot.r} fill="#e67300" fillOpacity={0.85} stroke="#fff" strokeWidth={0.5} style={{pointerEvents:"none"}} />
))}
{hm.xTicks.map((t,i)=><text key={`x${i}`} x={pad.left+(i/4)*w} y={height-20} fontSize={10} fill="#6a6a6a" textAnchor="middle">{t}</text>)}
<text x={pad.left+w/2} y={height-4} fontSize={11} fill="#0777b3" textAnchor="middle" fontWeight={600}
style={{cursor:"pointer"}} textDecoration="underline"
onClick={onXLabelClick}>{STAT_LABELS[xKey]} ▾</text>
{hm.yTicks.map((t,i)=><text key={`y${i}`} x={pad.left-5} y={pad.top+h-(i/4)*h+3} fontSize={10} fill="#6a6a6a" textAnchor="end">{t}</text>)}
<text x={8} y={pad.top+h/2} fontSize={11} fill="#0777b3" textAnchor="middle" fontWeight={600}
style={{cursor:"pointer"}} textDecoration="underline"
transform={`rotate(-90, 8, ${pad.top+h/2})`}
onClick={onYLabelClick}>{STAT_LABELS[yKey]} ▾</text>
{hover&&<g>
<rect x={Math.min(hover.px,width-100)} y={Math.max(0,hover.py-26)} width={80} height={20} rx={3} fill="#231f20" fillOpacity={0.9}/>
<text x={Math.min(hover.px,width-100)+6} y={Math.max(0,hover.py-26)+14} fontSize={10} fill="#fff">{hover.count} game{hover.count>1?"s":""}</text>
</g>}
</svg>
);
}
/* ── GQ Box Plot ─────────────────────────────────────────────────── */
function GQBoxPlot({ data, hlData, height=280 }) {
const w=60, pad={top:16,bottom:48,left:8,right:8};
const plotH=height-pad.top-pad.bottom;
const boxX=pad.left, boxW=w-pad.left-pad.right;
const stats=useMemo(()=>{
if(data.length===0) return null;
const vals=data.map(d=>d.game_quality).sort((a,b)=>a-b);
const n=vals.length;
const pct=(p)=>{const idx=p*(n-1),lo=Math.floor(idx),hi=Math.ceil(idx);return lo===hi?vals[lo]:vals[lo]+(vals[hi]-vals[lo])*(idx-lo);};
return {min:vals[0],p25:pct(0.25),p50:pct(0.5),p75:pct(0.75),max:vals[n-1]};
},[data]);
const playerTicks=useMemo(()=>hlData&&hlData.length>0?hlData.map(d=>d.game_quality):[],[hlData]);
if(!stats) return null;
const yScale=(v)=>pad.top+plotH-((v-stats.min)/(stats.max-stats.min||1))*plotH;
const cx=boxX+boxW/2, tw=8;
return (
<svg width={w} height={height} style={{display:"block",flexShrink:0}}>
<line x1={cx} y1={yScale(stats.min)} x2={cx} y2={yScale(stats.max)} stroke="#bbb" strokeWidth={1}/>
<line x1={cx-tw/2} y1={yScale(stats.min)} x2={cx+tw/2} y2={yScale(stats.min)} stroke="#bbb" strokeWidth={1}/>
<line x1={cx-tw/2} y1={yScale(stats.max)} x2={cx+tw/2} y2={yScale(stats.max)} stroke="#bbb" strokeWidth={1}/>
<rect x={boxX+4} y={yScale(stats.p75)} width={boxW-8} height={yScale(stats.p25)-yScale(stats.p75)} fill="#e8edf4" stroke="#9bb" strokeWidth={1}/>
<line x1={boxX+4} y1={yScale(stats.p50)} x2={boxX+boxW-4} y2={yScale(stats.p50)} stroke="#231f20" strokeWidth={2}/>
{playerTicks.map((gq,i)=><line key={i} x1={boxX+2} y1={yScale(gq)} x2={boxX+boxW-2} y2={yScale(gq)} stroke="#e67300" strokeWidth={1.5} strokeOpacity={0.8}/>)}
<text x={cx} y={height-4} fontSize={11} fill="#231f20" textAnchor="middle" fontWeight={600}>GQ</text>
</svg>
);
}
/* ── Axis Dropdown ───────────────────────────────────────────────── */
function AxisDropdown({ stats, labels, current, onSelect, position }) {
return (
<div style={{
position:"absolute", ...position,
background:"#fff", border:"1px solid #ccc", borderRadius:4,
boxShadow:"0 2px 8px rgba(0,0,0,0.15)", zIndex:20, padding:4,
}}>
{stats.map(s => (
<div key={s} onClick={() => onSelect(s)}
style={{
padding:"4px 10px", fontSize:11, cursor:"pointer", borderRadius:3,
whiteSpace:"nowrap",
background: current === s ? "#0777b3" : "transparent",
color: current === s ? "#fff" : "#231f20",
fontWeight: current === s ? 600 : 400,
}}>
{labels[s]}
</div>
))}
</div>
);
}
/* ── Table columns ───────────────────────────────────────────────── */
const TABLE_COLS = [
{ key: "team_id", label: "Team", sortKey: "team_id" },
{ key: "player_name", label: "Player", sortKey: "player_name" },
{ key: "opponent", label: "OPP", sortKey: "opponent" },
{ key: "game_date_short", label: "Date", sortKey: "game_date_short" },
{ key: "mins", label: "MIN", sortKey: "mins" },
{ key: "points", label: "PTS", sortKey: "points" },
{ key: "fg3_made", label: "3PTM", sortKey: "fg3_made" },
{ key: "fg_display", label: "FG%", sortKey: "fg_v" },
{ key: "ft_display", label: "FT%", sortKey: "ft_v" },
{ key: "rebounds", label: "REB", sortKey: "rebounds" },
{ key: "assists", label: "AST", sortKey: "assists" },
{ key: "steals", label: "STL", sortKey: "steals" },
{ key: "blocks", label: "BLK", sortKey: "blocks" },
{ key: "turnovers", label: "TO", sortKey: "turnovers" },
{ key: "game_quality", label: "GQ", sortKey: "game_quality" },
];
function fmtPctVal(pct, val) {
const sign = val >= 0 ? "+" : "";
return `${pct.toFixed(3)} (${sign}${val.toFixed(1)})`;
}
/* ── Main component ──────────────────────────────────────────────── */
export default function NBAGameQuality() {
const [filterText, setFilterText] = useState("");
const [filterMode, setFilterMode] = useState("edit");
const [xAxis, setXAxis] = useState("points");
const [yAxis, setYAxis] = useState("game_quality");
const [axisDropdown, setAxisDropdown] = useState(null);
const [sortCol, setSortCol] = useState("game_quality");
const [sortDir, setSortDir] = useState("desc");
const [page, setPage] = useState(0);
const [selectedBin, setSelectedBin] = useState(null);
const chartRef = useRef(null);
const inputRef = useRef(null);
const pendingCursor = useRef(null);
// Restore cursor position after controlled input updates
useEffect(() => {
if (pendingCursor.current !== null && inputRef.current) {
inputRef.current.setSelectionRange(pendingCursor.current, pendingCursor.current);
pendingCursor.current = null;
}
});
const SORTED_KEYS = useMemo(() => [...Object.keys(FILTER_KEYS), 'heatmap', 'dots', 'top'].sort(), []);
const AUTO_VALUES = { heatmap: 'true', dots: 'true' };
const handleFilterKeyDown = (e) => {
const input = e.target;
const pos = input.selectionStart;
const selEnd = input.selectionEnd;
if (e.key === 'Tab') {
e.preventDefault();
const before = filterText.slice(0, pos);
const lastSpace = before.lastIndexOf(' ');
const token = before.slice(lastSpace + 1).toLowerCase();
if (token && !token.includes(':')) {
const match = SORTED_KEYS.find(k => k.startsWith(token));
if (match) {
const autoVal = AUTO_VALUES[match];
const completion = autoVal ? match + ':' + autoVal : match + ':';
const newText = filterText.slice(0, lastSpace + 1) + completion + filterText.slice(pos);
setFilterText(newText);
pendingCursor.current = lastSpace + 1 + completion.length;
}
}
return;
}
if (e.key === '"') {
e.preventDefault();
// If cursor is right before a closing quote, just skip past it
if (pos === selEnd && filterText[pos] === '"') {
pendingCursor.current = pos + 1;
setFilterText(filterText); // trigger re-render to apply cursor
return;
}
// If text is selected, wrap selection in quotes
if (pos !== selEnd) {
const newText = filterText.slice(0, pos) + '"' + filterText.slice(pos, selEnd) + '"' + filterText.slice(selEnd);
setFilterText(newText);
pendingCursor.current = selEnd + 2;
return;
}
// Otherwise insert pair and place cursor between
const newText = filterText.slice(0, pos) + '""' + filterText.slice(pos);
setFilterText(newText);
pendingCursor.current = pos + 1;
return;
}
};
const parsedFilters = useMemo(() => parseFilters(filterText), [filterText]);
// Close axis dropdown on outside click
useEffect(() => {
if (!axisDropdown) return;
const handler = (e) => {
if (chartRef.current && !chartRef.current.contains(e.target)) setAxisDropdown(null);
};
document.addEventListener('mousedown', handler);
return () => document.removeEventListener('mousedown', handler);
}, [axisDropdown]);
const { data, isLoading, isError } = useSQLQuery(`
SELECT bg.player_name, bs.team_id,
CASE WHEN bs.team_id = s.home_team_abbreviation THEN s.away_team_abbreviation ELSE s.home_team_abbreviation END as opponent,
strftime(CAST(timezone('America/New_York', timezone('UTC', s.game_date)) AS DATE), '%m/%d/%y') as game_date_short,
CAST(substring(bs.minutes, 1, instr(bs.minutes, ':') - 1) AS INTEGER) as mins,
bg.points, bg.rebounds, bg.assists, bg.steals, bg.blocks, bg.turnovers, bg.fg3_made,
round(bg.fg_pct, 3) as fg_pct, round(bg.ft_pct, 3) as ft_pct,
round(bg.fg_v, 2) as fg_v, round(bg.ft_v, 2) as ft_v,
round(bg.game_quality, 4) as game_quality,
bg.game_id, bg.week_id
FROM "nba_box_scores"."main"."box_scores_gq" bg
JOIN "nba_box_scores"."main"."box_scores" bs ON bg.game_id = bs.game_id AND bg.entity_id = bs.entity_id AND bs.period = 'FullGame'
JOIN "nba_box_scores"."main"."schedule" s ON bg.game_id = s.game_id
WHERE bg.game_quality > 0 AND s.game_date >= '2025-10-20'
ORDER BY bg.game_quality DESC
`);
const cachedRef = useRef(null);
const cachedData = useMemo(() => {
const raw = Array.isArray(data) ? data : [];
if (raw.length === 0) return cachedRef.current || [];
if (cachedRef.current && cachedRef.current.length === raw.length) return cachedRef.current;
const processed = raw.map(d => {
const dp = String(d.game_date_short || '').split('/');
const gm = parseInt(dp[0], 10) || 0, gd = parseInt(dp[1], 10) || 0, gy = 2000 + (parseInt(dp[2], 10) || 0);
return {
player_name: d.player_name, team_id: d.team_id, opponent: d.opponent,
game_date_short: d.game_date_short, mins: N(d.mins),
points: N(d.points), rebounds: N(d.rebounds), assists: N(d.assists),
steals: N(d.steals), blocks: N(d.blocks), turnovers: N(d.turnovers),
fg3_made: N(d.fg3_made), fg_pct: N(d.fg_pct), ft_pct: N(d.ft_pct),
fg_v: N(d.fg_v), ft_v: N(d.ft_v), game_quality: N(d.game_quality),
game_id: d.game_id, week_id: N(d.week_id),
year: gy, month: gm, day: gd, week: gm && gd ? isoWeek(gy, gm, gd) : 0,
};
});
cachedRef.current = processed;
return processed;
}, [data]);
// Global axis bounds from full dataset (anchored regardless of filters)
const statBounds = useMemo(() => {
if (cachedData.length === 0) return null;
const b = {};
for (const s of STATS) {
const vals = cachedData.map(d => d[s]);
b[s] = { min: Math.min(...vals), max: Math.max(...vals) };
}
return b;
}, [cachedData]);
// Separate special directives from data filters
const heatmapFiltered = parsedFilters.some(f => f.key === 'heatmap' && f.value.toLowerCase() === 'true');
const dataFilters = useMemo(() => parsedFilters.filter(f => f.key !== 'heatmap' && f.key !== 'dots' && f.key !== 'top'), [parsedFilters]);
// Apply Datadog-style filters
const filteredDataRaw = useMemo(() => applyFilters(cachedData, dataFilters), [cachedData, dataFilters]);
// top:N directive — sort by current sort col/dir, then slice
const topN = useMemo(() => {
const tf = parsedFilters.find(f => f.key === 'top');
if (!tf) return 0;
const n = parseInt(tf.value, 10);
return isNaN(n) || n <= 0 ? 0 : n;
}, [parsedFilters]);
const filteredData = useMemo(() => {
if (!topN) return filteredDataRaw;
const s = sortCol;
const sorted = [...filteredDataRaw].sort((a, b) => {
if (s === "player_name" || s === "team_id" || s === "opponent" || s === "game_date_short") {
const cmp = (a[s]||"").localeCompare(b[s]||"");
return sortDir === "desc" ? -cmp : cmp;
}
return sortDir === "desc" ? b[s]-a[s] : a[s]-b[s];
});
return sorted.slice(0, topN);
}, [filteredDataRaw, topN, sortCol, sortDir]);
// Heatmap sees full dataset unless heatmap:true is set
const heatmapData = heatmapFiltered ? filteredData : cachedData;
// Orange highlight: show dots when a player filter is active or dots:true
const dotsEnabled = parsedFilters.some(f => f.key === 'dots' && f.value.toLowerCase() === 'true');
const playerFilter = dataFilters.find(f => f.key === 'player');
const hlData = useMemo(() => (playerFilter || dotsEnabled) ? filteredData : [], [filteredData, playerFilter, dotsEnabled]);
// Heatmap bin cross-filter (from heatmap's dataset, not table's)
const binFilteredGames = useMemo(() => {
if (!selectedBin) return null;
const { xi, yi, xMin, xStep, yMin, yStep, xKey, yKey } = selectedBin;
return filteredData.filter(d => {
const dxi = Math.min(HEATMAP_BINS-1, Math.floor((d[xKey]-xMin)/xStep));
const dyi = Math.min(HEATMAP_BINS-1, Math.floor((d[yKey]-yMin)/yStep));
return dxi === xi && dyi === yi;
});
}, [filteredData, selectedBin]);
const handleBinClick = (xi, yi, hm) => {
if (selectedBin && selectedBin.xi === xi && selectedBin.yi === yi) setSelectedBin(null);
else setSelectedBin({ xi, yi, xMin:hm.xMin, xStep:hm.xStep, yMin:hm.yMin, yStep:hm.yStep, xKey:xAxis, yKey:yAxis });
setPage(0);
};
const clearBin = () => { setSelectedBin(null); setPage(0); };
// Table data
const tableData = useMemo(() => {
const base = binFilteredGames || filteredData;
const s = sortCol;
if (s === "player_name" || s === "team_id" || s === "opponent" || s === "game_date_short") {
return [...base].sort((a, b) => { const cmp = (a[s]||"").localeCompare(b[s]||""); return sortDir === "desc" ? -cmp : cmp; });
}
return [...base].sort((a, b) => sortDir === "desc" ? b[s]-a[s] : a[s]-b[s]);
}, [filteredData, binFilteredGames, sortCol, sortDir]);
const totalPages = Math.max(1, Math.ceil(tableData.length / PAGE_SIZE));
const safeePage = Math.min(page, totalPages - 1);
const pageData = tableData.slice(safeePage * PAGE_SIZE, (safeePage + 1) * PAGE_SIZE);
const handleSort = (sortKey) => {
if (sortCol === sortKey) setSortDir(d => d === "desc" ? "asc" : "desc");
else { setSortCol(sortKey); setSortDir("desc"); }
setPage(0);
};
// Click a table row to toggle player filter in the text box
const handleRowClick = (playerName) => {
const escaped = playerName.includes(' ') ? `player:"${playerName}"` : `player:${playerName}`;
const isAlready = parsedFilters.some(f => f.key === 'player' && f.value.toLowerCase() === playerName.toLowerCase());
if (isAlready) {
setFilterText(prev =>
prev
.replace(new RegExp(`player:"${playerName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}"`, 'gi'), '')
.replace(new RegExp(`player:${playerName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}(?=[\\s]|$)`, 'gi'), '')
.replace(/\s+/g, ' ').trim()
);
} else {
setFilterText(prev => (prev.trim() ? prev.trim() + ' ' : '') + escaped);
}
setPage(0);
};
const btnStyle = (dis) => ({fontSize:11,padding:"4px 8px",borderRadius:4,border:"1px solid #ccc",background:"#fff",cursor:dis?"not-allowed":"pointer",opacity:dis?0.3:1});
const filterCount = dataFilters.length;
return (
<div style={{background:"#f8f8f8",color:"#231f20",maxWidth:900,fontFamily:"system-ui, sans-serif"}}>
<div style={{padding:20}}>
<h1 style={{fontSize:20,fontWeight:700,marginBottom:4}}>NBA Game Quality Explorer — 2025-26</h1>
<p style={{fontSize:14,color:"#6a6a6a",marginBottom:16}}>Individual game performances ranked by Game Quality — a head-to-head dominance score across 9 stat categories within each week.</p>
{/* ── Filter bar ──────────────────────────────────────────── */}
<div style={{display:"flex",gap:8,alignItems:"stretch",marginBottom:16}}>
<div style={{flex:1,position:"relative"}}>
{filterMode === 'edit' ? (
<input ref={inputRef} type="text" value={filterText}
onChange={e => { setFilterText(e.target.value); setPage(0); setSelectedBin(null); }}
onKeyDown={handleFilterKeyDown}
placeholder='filter on key:"value" e.g. team:LAL player:"LeBron James" opp:BOS'
style={{fontSize:13,border:"1px solid #ccc",borderRadius:4,padding:"7px 10px",background:"#fff",width:"100%",fontFamily:"'SF Mono',SFMono-Regular,Consolas,monospace",boxSizing:"border-box"}} />
) : (
<div style={{fontSize:13,border:"1px solid #ccc",borderRadius:4,padding:"7px 10px",background:"#f0f0f0",fontFamily:"'SF Mono',SFMono-Regular,Consolas,monospace",color:"#231f20",minHeight:32,whiteSpace:"pre-wrap",boxSizing:"border-box"}}>
{filtersToSQL(parsedFilters)}
</div>
)}
{filterText && filterMode === 'edit' && (
<span style={{position:"absolute",right:8,top:"50%",transform:"translateY(-50%)",cursor:"pointer",fontSize:13,color:"#999",lineHeight:1}}
onClick={() => { setFilterText(""); setPage(0); setSelectedBin(null); }}>✕</span>
)}
</div>
<div style={{display:"flex",gap:8,flexShrink:0,alignItems:"center"}}>
<span onClick={() => setFilterMode('edit')}
style={{fontSize:11,cursor:"pointer",color:"#6a6a6a",
textDecoration:filterMode==='edit'?'underline':'none',
textUnderlineOffset:3}}>
edit
</span>
<span onClick={() => setFilterMode('sql')}
style={{fontSize:11,cursor:"pointer",color:"#6a6a6a",
textDecoration:filterMode==='sql'?'underline':'none',
textUnderlineOffset:3}}>
SQL
</span>
</div>
</div>
{isLoading ? (
<div style={{display:"flex",alignItems:"center",justifyContent:"center",padding:"64px 0",fontSize:14,color:"#6a6a6a"}}>Loading game data...</div>
) : isError ? (
<div style={{display:"flex",alignItems:"center",justifyContent:"center",padding:"64px 0",fontSize:14,color:"#bc1200"}}>Error loading data. Please try refreshing.</div>
) : (
<>
<p style={{fontSize:11,color:"#6a6a6a",marginBottom:4}}>
{STAT_LABELS[xAxis]} vs {STAT_LABELS[yAxis]} — {heatmapData.length} games{heatmapFiltered ? ' (heatmap filtered)' : ''}.
{' '}Darker = more games. Click a cell to filter the table.
{(playerFilter || dotsEnabled) && <span> Orange dots: filtered games.</span>}
{selectedBin && <span style={{fontWeight:600}}> Cell selected — <span style={{cursor:"pointer",textDecoration:"underline"}} onClick={clearBin}>clear</span></span>}
</p>
{/* ── Chart area with axis dropdowns ────────────────── */}
<div ref={chartRef} style={{position:"relative"}}>
<div style={{display:"flex",alignItems:"flex-start",justifyContent:"space-between"}}>
<Heatmap data={heatmapData} xKey={xAxis} yKey={yAxis} hlData={hlData}
selectedBin={selectedBin} onBinClick={handleBinClick}
onXLabelClick={() => setAxisDropdown(axisDropdown === 'x' ? null : 'x')}
onYLabelClick={() => setAxisDropdown(axisDropdown === 'y' ? null : 'y')}
bounds={statBounds ? { xMin: statBounds[xAxis].min, xMax: statBounds[xAxis].max, yMin: statBounds[yAxis].min, yMax: statBounds[yAxis].max } : { xMin:0, xMax:1, yMin:0, yMax:1 }}
width={800} height={280} />
<GQBoxPlot data={heatmapData} hlData={hlData} height={280} />
</div>
{axisDropdown === 'x' && (
<AxisDropdown stats={STATS} labels={STAT_LABELS} current={xAxis}
onSelect={s => { setXAxis(s); setAxisDropdown(null); clearBin(); }}
position={{bottom:-4,left:370}} />
)}
{axisDropdown === 'y' && (
<AxisDropdown stats={STATS} labels={STAT_LABELS} current={yAxis}
onSelect={s => { setYAxis(s); setAxisDropdown(null); clearBin(); }}
position={{top:60,left:0}} />
)}
</div>
<div style={{display:"flex",alignItems:"center",gap:4,marginTop:4,marginBottom:16}}>
<span style={{fontSize:11,color:"#6a6a6a"}}>Fewer</span>
{HEATMAP_COLORS.filter((_,i)=>i%3===0).map((c,i)=><div key={i} style={{width:14,height:8,background:c,borderRadius:1}} />)}
<span style={{fontSize:11,color:"#6a6a6a"}}>More games</span>
</div>
{/* ── Table ──────────────────────────────────────────── */}
<div style={{overflowX:"auto"}}>
<div style={{display:"flex",alignItems:"center",justifyContent:"space-between",marginBottom:8}}>
<p style={{fontSize:11,color:"#6a6a6a"}}>
{tableData.length} games{selectedBin?" — cell filter":""}
{totalPages>1?` — Page ${safeePage+1}/${totalPages}`:""}
</p>
<div style={{display:"flex",gap:8}}>
<button onClick={()=>setPage(p=>Math.max(0,p-1))} disabled={safeePage===0||totalPages<=1} style={btnStyle(safeePage===0||totalPages<=1)}>← Prev</button>
<button onClick={()=>setPage(p=>Math.min(totalPages-1,p+1))} disabled={safeePage>=totalPages-1||totalPages<=1} style={btnStyle(safeePage>=totalPages-1||totalPages<=1)}>Next →</button>
</div>
</div>
<table style={{width:"100%",fontSize:11,borderCollapse:"collapse"}}>
<thead>
<tr style={{borderBottom:"2px solid #231f20"}}>
<th style={{textAlign:"left",padding:"8px 4px",fontWeight:600,width:24}}>#</th>
{TABLE_COLS.map(col=>(
<th key={col.key} style={{textAlign:["player_name","team_id","opponent","game_date_short"].includes(col.key)?"left":"right",padding:"8px 4px",fontWeight:600,cursor:"pointer",whiteSpace:"nowrap",userSelect:"none"}}
onClick={()=>handleSort(col.sortKey)}>
{col.label}{sortCol===col.sortKey?(sortDir==="desc"?" ↓":" ↑"):""}
</th>
))}
</tr>
</thead>
<tbody>
{pageData.map((g,i)=>{
const idx=safeePage*PAGE_SIZE+i;
const isHl = dotsEnabled || (playerFilter && g.player_name.toLowerCase().includes(playerFilter.value.toLowerCase()));
return (
<tr key={g.game_id+i} style={{borderBottom:"1px solid #e5e5e5",cursor:"pointer",background:isHl?"#fef3e2":idx%2===0?"transparent":"#f0f0f0"}}
onClick={()=>handleRowClick(g.player_name)}>
<td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{idx+1}</td>
<td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{g.team_id}</td>
<td style={{padding:"4px",lineHeight:"20px",fontWeight:500,minWidth:120}}>{g.player_name}</td>
<td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{g.opponent}</td>
<td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{g.game_date_short}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.mins}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right",fontWeight:700}}>{g.points}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.fg3_made}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right",whiteSpace:"nowrap"}}>{fmtPctVal(g.fg_pct,g.fg_v)}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right",whiteSpace:"nowrap"}}>{fmtPctVal(g.ft_pct,g.ft_v)}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.rebounds}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.assists}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.steals}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.blocks}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.turnovers}</td>
<td style={{padding:"4px",lineHeight:"20px",textAlign:"right",fontWeight:700}}>{g.game_quality.toFixed(4)}</td>
</tr>
);
})}
</tbody>
</table>
<div style={{display:"flex",justifyContent:"flex-end",marginTop:8,gap:8}}>
<button onClick={()=>setPage(p=>Math.max(0,p-1))} disabled={safeePage===0||totalPages<=1} style={btnStyle(safeePage===0||totalPages<=1)}>← Prev</button>
<button onClick={()=>setPage(p=>Math.min(totalPages-1,p+1))} disabled={safeePage>=totalPages-1||totalPages<=1} style={btnStyle(safeePage>=totalPages-1||totalPages<=1)}>Next →</button>
</div>
</div>
<p style={{fontSize:11,color:"#6a6a6a",marginTop:16}}>
<strong>Keys:</strong> team, player, opp, date, pts, reb, ast, stl, blk, to, 3ptm, min, gq, fg%, ft%, year, month, day, week, top — e.g. <code style={{background:"#e8e8e8",padding:"1px 4px",borderRadius:2}}>team:LA month:&gt;=12 top:10</code>. <code style={{background:"#e8e8e8",padding:"1px 4px",borderRadius:2}}>top:N</code> limits to top N by current sort. Click axis labels to change stats. Click rows to toggle player filter. Add <code style={{background:"#e8e8e8",padding:"1px 4px",borderRadius:2}}>heatmap:true</code> to apply filters to heatmap.
</p>
</>
)}
</div>
</div>
);
}
$abcd$,
description = 'Interactive NBA Game Quality explorer with Datadog-style key:value filter bar (Tab autocomplete, auto-close quotes, IN lists with bracket syntax, numeric comparisons, date part filters), edit/SQL toggle, 20x20 heatmap with contour edges and anchored axes, clickable axis label dropdowns, GQ box plot with player tick marks, orange dot overlay via player filter or dots:true directive, heatmap:true to apply filters to heatmap, sortable paginated table with click-to-filter rows.',
api_version = 1
);
Raw
create_ssms_dive.sql
SELECT * FROM MD_CREATE_DIVE(
title = 'NBA Point Guards - SSMS Edition',
content = $dive$
import { useState, useCallback, useMemo, useEffect, useRef } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";
const N = (v) => (v != null ? Number(v) : 0);
const fmtTimer = (ms) => {
const s = Math.floor(ms/1000);
return `${String(Math.floor(s/3600)).padStart(2,'0')}:${String(Math.floor((s%3600)/60)).padStart(2,'0')}:${String(s%60).padStart(2,'0')}`;
};
const S = {bg:"#F0F0F0",menuBg:"#E8E8E8",menuBdr:"#CCC",toolbar:"#D6DBE9",white:"#FFF",gridHdr:"#E8E8EC",gridBdr:"#C6C6C6",altRow:"#F5F5FF",sel:"#3399FF",bar:"#007ACC",text:"#1E1E1E",muted:"#6D6D6D",kw:"#0000FF",str:"#A31515",cmt:"#008000",fn:"#FF00FF",ln:"#2B91AF",tabOn:"#FFF",tabOff:"#DCDDDE",hover:"#E8EDFA",treeSel:"#CCE8FF"};
const TC = {keyword:S.kw,string:S.str,comment:S.cmt,func:S.fn,plain:S.text};
const PG_COLS = [{k:"idx",l:"",w:28},{k:"player_name",l:"player_name",w:190},{k:"team_id",l:"team_id",w:55},{k:"games",l:"games",w:48},{k:"ppg",l:"ppg",w:48},{k:"apg",l:"apg",w:45},{k:"rpg",l:"rpg",w:45},{k:"spg",l:"spg",w:45},{k:"topg",l:"topg",w:45},{k:"fg_pct",l:"fg_pct",w:56},{k:"fg3_pct",l:"fg3_pct",w:56},{k:"ft_pct",l:"ft_pct",w:56},{k:"ast_tov",l:"ast_tov",w:60},{k:"ts_pct",l:"ts_pct",w:52}];
const SORTS = [{k:"ppg",l:"PPG"},{k:"apg",l:"APG"},{k:"rpg",l:"RPG"},{k:"spg",l:"SPG"},{k:"ts_pct",l:"TS%"},{k:"ast_tov",l:"AST/TO"},{k:"fg_pct",l:"FG%"},{k:"fg3_pct",l:"3P%"},{k:"ft_pct",l:"FT%"}];
const PG_LIST = "'Shai Gilgeous-Alexander','Anthony Edwards','Tyrese Maxey','Stephen Curry','Jalen Brunson','Jamal Murray','James Harden','Devin Booker','Cade Cunningham','Scottie Barnes','De''Aaron Fox','Ja Morant','LaMelo Ball','Darius Garland','Derrick White','Immanuel Quickley','Jrue Holiday','Anfernee Simons','D''Angelo Russell'";
const DB = "nba_box_scores";
const TABLES = [
{id:"t1",l:"box_scores",tbl:"box_scores"},
{id:"t2",l:"box_score_gq",tbl:"box_score_gq"},
{id:"t3",l:"box_score_gq_v2",tbl:"box_score_gq_v2"},
{id:"t4",l:"schedule",tbl:"schedule"},
{id:"t5",l:"team_stats",tbl:"team_stats"},
{id:"t6",l:"minutes_test",tbl:"minutes_test"},
];
const VIEWS = [{id:"v1",l:"box_scores_gq",tbl:"box_scores_gq"}];
const TREE_NODES = [
{id:"root",l:`${DB} (MotherDuck)`,ic:"🗄️",ch:["dbs"],open:true},
{id:"dbs",l:"Databases",ic:"📁",ch:["db"],open:true},
{id:"db",l:DB,ic:"🛢️",ch:["tf","vf"],open:true},
{id:"tf",l:"Tables",ic:"📁",ch:TABLES.map(t=>t.id),open:true},
...TABLES.map(t=>({id:t.id,l:t.l,ic:"📋",tbl:t.tbl})),
{id:"vf",l:"Views",ic:"📁",ch:VIEWS.map(v=>v.id),open:false},
...VIEWS.map(v=>({id:v.id,l:v.l,ic:"👁️",tbl:v.tbl})),
];
const CTX_ITEMS = [
{l:"Select Top 100 Rows",ic:"📋",action:"top100"},
{l:"Select Top 1000 Rows",ic:"📋",action:"top1000"},
{sep:true},
{l:"Script Table as",ic:"📜",sub:"▶",disabled:true},
{sep:true},
{l:"Edit Top 200 Rows",ic:"✏️",disabled:true},
{l:"Design",ic:"🔧",disabled:true},
{sep:true},
{l:"Policies",ic:"🛡️",sub:"▶",disabled:true},
{sep:true},
{l:"Rename",disabled:true},
{l:"Delete",disabled:true},
{l:"Refresh",ic:"🔄"},
{sep:true},
{l:"Properties",ic:"⚙️",disabled:true},
];
function ContextMenu({x,y,tbl,onAction,onClose}){
const [hov,setHov]=useState(null);
const ref=useRef(null);
useEffect(()=>{
const h=(e)=>{if(ref.current&&!ref.current.contains(e.target))onClose();};
document.addEventListener("mousedown",h);
return ()=>document.removeEventListener("mousedown",h);
},[onClose]);
return(
<div ref={ref} style={{position:"fixed",left:x,top:y,zIndex:9999,background:S.white,border:`1px solid ${S.gridBdr}`,boxShadow:"2px 2px 6px rgba(0,0,0,0.2)",padding:"2px 0",minWidth:220,fontFamily:"Segoe UI,Tahoma,sans-serif",fontSize:12}}>
{CTX_ITEMS.map((it,i)=>it.sep
?<div key={i} style={{borderTop:`1px solid ${S.gridBdr}`,margin:"2px 0"}}/>
:<div key={i} onMouseEnter={()=>setHov(i)} onMouseLeave={()=>setHov(null)}
onClick={()=>{if(!it.disabled&&it.action){onAction(it.action,tbl);onClose();}}}
style={{padding:"3px 24px 3px 8px",display:"flex",alignItems:"center",gap:6,cursor:it.disabled?"default":"pointer",
background:hov===i&&!it.disabled?"#CCE8FF":"transparent",color:it.disabled?S.muted:S.text}}>
<span style={{width:16,fontSize:11,textAlign:"center"}}>{it.ic||""}</span>
<span style={{flex:1}}>{it.l}</span>
{it.sub&&<span style={{color:S.muted,fontSize:10}}>{it.sub}</span>}
</div>
)}
</div>
);
}
function Tree({onOpen,onCtxMenu}) {
const m={}; TREE_NODES.forEach(n=>{m[n.id]=n});
const d={}; TREE_NODES.forEach(n=>{if(n.open!==undefined)d[n.id]=n.open});
const [exp,setExp]=useState(d);
const [hov,setHov]=useState(null);
const [sel,setSel]=useState(null);
const go=(id,depth=0)=>{
const n=m[id]; if(!n)return null;
const hasCh=n.ch&&n.ch.length>0;
return(<div key={id}>
<div onMouseEnter={()=>setHov(id)} onMouseLeave={()=>setHov(null)}
onClick={()=>{setSel(id);if(hasCh)setExp(p=>({...p,[id]:!p[id]}));else if(n.tbl)onOpen(n.tbl);}}
onContextMenu={(e)=>{if(n.tbl){e.preventDefault();setSel(id);onCtxMenu(e.clientX,e.clientY,n.tbl);}}}
style={{display:"flex",alignItems:"center",gap:3,padding:`2px 6px 2px ${4+depth*16}px`,cursor:"pointer",whiteSpace:"nowrap",fontSize:11.5,background:sel===id?S.treeSel:hov===id?S.hover:"transparent",borderLeft:sel===id?`2px solid ${S.bar}`:"2px solid transparent"}}>
{hasCh?<span style={{fontSize:8,color:S.muted,width:10,textAlign:"center"}}>{exp[id]?"▼":"▶"}</span>:<span style={{width:10}}/>}
<span>{n.ic}</span><span style={{fontWeight:n.tbl==="box_scores"?"bold":"normal"}}>{n.l}</span>
</div>
{hasCh&&exp[id]&&n.ch.map(c=>go(c,depth+1))}
</div>);
};
return <div style={{padding:"2px 0"}}>{go("root")}</div>;
}
function pgSql(sk,sd){return[
{n:1,t:[{c:"keyword",v:"SELECT "},{c:"plain",v:"b.player_name, b.team_id,"}]},
{n:2,t:[{c:"plain",v:" "},{c:"func",v:"COUNT"},{c:"plain",v:"(*) "},{c:"keyword",v:"AS "},{c:"plain",v:"games,"}]},
{n:3,t:[{c:"plain",v:" "},{c:"func",v:"ROUND"},{c:"plain",v:"("},{c:"func",v:"AVG"},{c:"plain",v:"(b.points), 1) "},{c:"keyword",v:"AS "},{c:"plain",v:"ppg,"}]},
{n:4,t:[{c:"plain",v:" "},{c:"func",v:"ROUND"},{c:"plain",v:"("},{c:"func",v:"AVG"},{c:"plain",v:"(b.assists), 1) "},{c:"keyword",v:"AS "},{c:"plain",v:"apg,"}]},
{n:5,t:[{c:"plain",v:" "},{c:"comment",v:"-- ... rpg, spg, topg, fg_pct, fg3_pct, ft_pct, ast_tov, ts_pct"}]},
{n:6,t:[{c:"keyword",v:"FROM "},{c:"plain",v:`"${DB}"."main"."box_scores" b`}]},
{n:7,t:[{c:"keyword",v:"JOIN "},{c:"plain",v:`"${DB}"."main"."schedule" s `},{c:"keyword",v:"ON "},{c:"plain",v:"b.game_id = s.game_id"}]},
{n:8,t:[{c:"keyword",v:"WHERE "},{c:"plain",v:"b.period = "},{c:"string",v:"'FullGame'"}]},
{n:9,t:[{c:"plain",v:" "},{c:"keyword",v:"AND "},{c:"plain",v:"s.game_date >= "},{c:"string",v:"'2025-10-20'"}]},
{n:10,t:[{c:"plain",v:" "},{c:"keyword",v:"AND "},{c:"plain",v:"b.player_name "},{c:"keyword",v:"IN "},{c:"plain",v:"("},{c:"comment",v:"/* 19 point guards */"},{c:"plain",v:")"}]},
{n:11,t:[{c:"keyword",v:"GROUP BY "},{c:"plain",v:"b.player_name, b.team_id"}]},
{n:12,t:[{c:"keyword",v:"HAVING "},{c:"func",v:"COUNT"},{c:"plain",v:"(*) >= 20"}]},
{n:13,t:[{c:"keyword",v:"ORDER BY "},{c:"plain",v:`${sk} ${sd.toUpperCase()}`}]},
];}
function tblSql(name){return[
{n:1,t:[{c:"keyword",v:"SELECT "},{c:"plain",v:"*"}]},
{n:2,t:[{c:"keyword",v:"FROM "},{c:"plain",v:`"${DB}"."main"."${name}"`}]},
{n:3,t:[{c:"keyword",v:"LIMIT "},{c:"plain",v:"100"}]},
];}
function Sql({lines}){return(
<div style={{background:S.white,overflow:"auto",minHeight:46,maxHeight:195,fontFamily:"Consolas,'Courier New',monospace",fontSize:13,lineHeight:"20px",borderBottom:`2px solid ${S.gridBdr}`}}>
{lines.map(l=><div key={l.n} style={{display:"flex",whiteSpace:"pre"}}>
<span style={{width:34,textAlign:"right",paddingRight:8,color:S.ln,background:"#F5F5F5",borderRight:"1px solid #E0E0E0",flexShrink:0}}>{l.n}</span>
<span style={{paddingLeft:8}}>{l.t.map((tk,j)=><span key={j} style={{color:TC[tk.c]}}>{tk.v}</span>)}</span>
</div>)}
</div>
);}
function Skeleton(){return(
<div style={{padding:16,display:"flex",flexDirection:"column",gap:8}}>
<div style={{display:"flex",gap:4,padding:"4px 0",borderBottom:`1px solid ${S.gridBdr}`}}>
{[80,120,60,60,60,60,60].map((w,i)=><div key={i} className="animate-pulse" style={{height:14,width:w,background:"#E0E0E0",borderRadius:2}}/>)}
</div>
{[...Array(6)].map((_,i)=><div key={i} style={{display:"flex",gap:4}}>
{[80,120,60,60,60,60,60].map((w,j)=><div key={j} className="animate-pulse" style={{height:12,width:w,background:i%2===0?"#F0F0F0":"#E8E8F0",borderRadius:2}}/>)}
</div>)}
</div>
);}
function SqlEditor({value,onChange,onExecute}){
const lines=value.split('\n').length;
return(
<div style={{display:"flex",background:S.white,overflow:"auto",minHeight:46,maxHeight:195,borderBottom:`2px solid ${S.gridBdr}`,fontFamily:"Consolas,'Courier New',monospace",fontSize:13,lineHeight:"20px"}}>
<div style={{width:34,flexShrink:0,background:"#F5F5F5",borderRight:"1px solid #E0E0E0",paddingTop:2,textAlign:"right",paddingRight:8,color:S.ln,userSelect:"none"}}>
{[...Array(Math.max(lines,1))].map((_,i)=><div key={i}>{i+1}</div>)}
</div>
<textarea value={value} onChange={e=>onChange(e.target.value)} spellCheck={false}
onKeyDown={e=>{if(e.key==="Enter"&&(e.ctrlKey||e.metaKey)){e.preventDefault();onExecute();}}}
style={{flex:1,border:"none",outline:"none",resize:"none",padding:"2px 8px",fontFamily:"inherit",fontSize:"inherit",lineHeight:"inherit",background:"transparent",color:S.text,minHeight:40}}
placeholder="-- Type your SQL here, then press Ctrl+Enter or click Execute"/>
</div>
);
}
function CustomResults({sql,onExecuting}){
const {data,isLoading,isError,error}=useSQLQuery(sql,{enabled:!!sql});
useEffect(()=>{onExecuting?.(isLoading);},[isLoading]);
const rows=Array.isArray(data)?data:[];
if(!sql) return <div style={{padding:12,fontFamily:"Consolas,monospace",fontSize:12,color:S.muted}}>Press Ctrl+Enter or click ▶ Execute to run your query.</div>;
if(isLoading) return <Skeleton/>;
if(isError) return <div style={{padding:12,color:"#bc1200",fontFamily:"Consolas,monospace",fontSize:12}}>Msg 102, Level 15, State 1<br/>{String(error)}</div>;
if(rows.length===0) return <div style={{padding:12,fontFamily:"Consolas,monospace",fontSize:12,color:S.muted}}>Command(s) completed successfully.<br/>(0 rows affected)</div>;
const cols=Object.keys(rows[0]);
return(
<table style={{borderCollapse:"collapse",width:"100%",fontFamily:"Consolas,monospace",fontSize:11}}>
<thead><tr>
<th style={{background:S.gridHdr,border:`1px solid ${S.gridBdr}`,padding:"3px 5px",fontWeight:"bold",textAlign:"right",position:"sticky",top:0,whiteSpace:"nowrap",minWidth:28,fontSize:11,zIndex:1,color:S.muted}}></th>
{cols.map(c=><th key={c} style={{background:S.gridHdr,border:`1px solid ${S.gridBdr}`,padding:"3px 5px",fontWeight:"bold",textAlign:"left",position:"sticky",top:0,whiteSpace:"nowrap",minWidth:70,fontSize:11,zIndex:1}}>{c}</th>)}
</tr></thead>
<tbody>{rows.map((r,i)=><tr key={i} style={{background:i%2===0?S.white:S.altRow}}>
<td style={{border:`1px solid ${S.gridBdr}`,padding:"2px 5px",textAlign:"right",color:S.muted}}>{i+1}</td>
{cols.map(c=><td key={c} style={{border:`1px solid ${S.gridBdr}`,padding:"2px 5px",whiteSpace:"nowrap",textAlign:typeof N(r[c])==="number"&&r[c]!==null&&!isNaN(Number(r[c]))?"right":"left",maxWidth:250,overflow:"hidden",textOverflow:"ellipsis"}}>{r[c]!=null?String(r[c]):""}</td>)}
</tr>)}</tbody>
</table>
);
}
function CustomMessages({sql,rows}){
return(
<div style={{padding:12,fontFamily:"Consolas,monospace",fontSize:12,lineHeight:"18px"}}>
{sql?<>
<div style={{color:S.cmt}}>/* User Query — wrapped in LIMIT 100 */</div>
<div style={{marginTop:6}}>Command(s) completed successfully.</div>
<div style={{color:S.muted}}>({rows} rows returned)</div>
</>:<div style={{color:S.muted}}>No query executed yet.</div>}
<div style={{marginTop:10}}>Completion time: {new Date().toISOString().replace('Z','')}</div>
</div>
);
}
function TablePreview({tableName,onExecuting}){
const {data,isLoading,isError,error} = useSQLQuery(`SELECT * FROM "${DB}"."main"."${tableName}" LIMIT 100`);
useEffect(()=>{onExecuting?.(isLoading);},[isLoading]);
const rows = Array.isArray(data) ? data : [];
if(isLoading) return <Skeleton/>;
if(isError) return <div style={{padding:12,color:"#bc1200",fontFamily:"Consolas,monospace",fontSize:12}}>Msg 208, Level 16, State 1<br/>Error: {String(error)}</div>;
if(rows.length===0) return <div style={{padding:12,fontFamily:"Consolas,monospace",fontSize:12,color:S.muted}}>(0 rows affected)</div>;
const cols = Object.keys(rows[0]);
return(
<table style={{borderCollapse:"collapse",width:"100%",fontFamily:"Consolas,monospace",fontSize:11}}>
<thead><tr>
<th style={{background:S.gridHdr,border:`1px solid ${S.gridBdr}`,padding:"3px 5px",fontWeight:"bold",textAlign:"right",position:"sticky",top:0,whiteSpace:"nowrap",minWidth:28,fontSize:11,zIndex:1,color:S.muted}}></th>
{cols.map(c=><th key={c} style={{background:S.gridHdr,border:`1px solid ${S.gridBdr}`,padding:"3px 5px",fontWeight:"bold",textAlign:"left",position:"sticky",top:0,whiteSpace:"nowrap",minWidth:70,fontSize:11,zIndex:1}}>{c}</th>)}
</tr></thead>
<tbody>{rows.map((r,i)=><tr key={i} style={{background:i%2===0?S.white:S.altRow}}>
<td style={{border:`1px solid ${S.gridBdr}`,padding:"2px 5px",textAlign:"right",color:S.muted}}>{i+1}</td>
{cols.map(c=><td key={c} style={{border:`1px solid ${S.gridBdr}`,padding:"2px 5px",whiteSpace:"nowrap",textAlign:typeof N(r[c])==="number"&&r[c]!==null&&!isNaN(Number(r[c]))?"right":"left",maxWidth:250,overflow:"hidden",textOverflow:"ellipsis"}}>{r[c]!=null?String(r[c]):""}</td>)}
</tr>)}</tbody>
</table>
);
}
const PG_SQL = `
SELECT b.player_name, b.team_id,
COUNT(*) as games,
ROUND(AVG(b.points),1) as ppg, ROUND(AVG(b.assists),1) as apg,
ROUND(AVG(b.rebounds),1) as rpg, ROUND(AVG(b.steals),1) as spg,
ROUND(AVG(b.turnovers),1) as topg,
ROUND(SUM(b.fg_made)*1.0/NULLIF(SUM(b.fg_attempted),0)*100,1) as fg_pct,
ROUND(SUM(b.fg3_made)*1.0/NULLIF(SUM(b.fg3_attempted),0)*100,1) as fg3_pct,
ROUND(SUM(b.ft_made)*1.0/NULLIF(SUM(b.ft_attempted),0)*100,1) as ft_pct,
ROUND(SUM(b.assists)*1.0/NULLIF(SUM(b.turnovers),0),2) as ast_tov,
ROUND(SUM(b.points)*1.0/NULLIF(2*(SUM(b.fg_attempted)+0.44*SUM(b.ft_attempted)),0)*100,1) as ts_pct
FROM "${DB}"."main"."box_scores" b
JOIN "${DB}"."main"."schedule" s ON b.game_id = s.game_id
WHERE b.period='FullGame' AND s.game_date >= '2025-10-20'
AND b.player_name IN (${PG_LIST})
GROUP BY b.player_name, b.team_id
HAVING COUNT(*) >= 20
`;
function PGAnalysis({sortKey,sortDir,onHeaderClick,selRow,onRowClick,onExecuting}){
const {data,isLoading,isError,error} = useSQLQuery(PG_SQL);
useEffect(()=>{onExecuting?.(isLoading);},[isLoading]);
const rows = Array.isArray(data) ? data : [];
const pgRows = useMemo(()=>{
const mapped = rows.map(r=>({player_name:r.player_name,team_id:r.team_id,games:N(r.games),
ppg:N(r.ppg),apg:N(r.apg),rpg:N(r.rpg),spg:N(r.spg),topg:N(r.topg),
fg_pct:N(r.fg_pct),fg3_pct:N(r.fg3_pct),ft_pct:N(r.ft_pct),ast_tov:N(r.ast_tov),ts_pct:N(r.ts_pct)}));
const mult = sortDir==="desc"?-1:1;
mapped.sort((a,b)=>mult*(a[sortKey]-b[sortKey]));
return mapped.map((r,i)=>({...r,idx:i+1,
ppg:r.ppg.toFixed(1),apg:r.apg.toFixed(1),rpg:r.rpg.toFixed(1),spg:r.spg.toFixed(1),topg:r.topg.toFixed(1),
fg_pct:r.fg_pct.toFixed(1),fg3_pct:r.fg3_pct.toFixed(1),ft_pct:r.ft_pct.toFixed(1),ast_tov:r.ast_tov.toFixed(2),ts_pct:r.ts_pct.toFixed(1)}));
},[rows,sortKey,sortDir]);
if(isLoading) return <Skeleton/>;
if(isError) return <div style={{padding:12,color:"#bc1200",fontFamily:"Consolas,monospace",fontSize:12}}>Msg 208, Level 16, State 1<br/>Error: {String(error)}</div>;
return(
<table style={{borderCollapse:"collapse",width:"100%",fontFamily:"Consolas,monospace",fontSize:11}}>
<thead><tr>{PG_COLS.map(c=><th key={c.k} onClick={()=>onHeaderClick(c.k)} style={{background:S.gridHdr,border:`1px solid ${S.gridBdr}`,padding:"3px 5px",fontWeight:"bold",textAlign:"left",position:"sticky",top:0,cursor:"pointer",whiteSpace:"nowrap",minWidth:c.w,fontSize:11,zIndex:1,color:sortKey===c.k?S.kw:S.text}}>{c.l}{sortKey===c.k&&<span style={{marginLeft:2,fontSize:9}}>{sortDir==="desc"?"▼":"▲"}</span>}</th>)}</tr></thead>
<tbody>{pgRows.map((r,i)=><tr key={i} onClick={()=>onRowClick(i)} style={{background:selRow===i?S.sel:i%2===0?S.white:S.altRow,color:selRow===i?"#FFF":S.text,cursor:"pointer"}}>
{PG_COLS.map(c=><td key={c.k} style={{border:`1px solid ${S.gridBdr}`,padding:"2px 5px",whiteSpace:"nowrap",textAlign:c.k==="player_name"||c.k==="team_id"?"left":"right",fontWeight:c.k==="player_name"&&i<3?"bold":sortKey===c.k?"bold":"normal",color:selRow===i?"#FFF":c.k==="idx"?S.muted:S.text}}>{r[c.k]}</td>)}
</tr>)}</tbody>
</table>
);
}
function PGMessages({sortKey,sortDir}){
const {data} = useSQLQuery(PG_SQL);
const rows = Array.isArray(data) ? data : [];
const sorted = useMemo(()=>{
const mapped = rows.map(r=>({...r,_ppg:N(r.ppg),_apg:N(r.apg),_rpg:N(r.rpg),_spg:N(r.spg),_fg_pct:N(r.fg_pct),_fg3_pct:N(r.fg3_pct),_ft_pct:N(r.ft_pct),_ast_tov:N(r.ast_tov),_ts_pct:N(r.ts_pct)}));
const mult = sortDir==="desc"?-1:1;
mapped.sort((a,b)=>mult*(N(a[sortKey])-N(b[sortKey])));
return mapped;
},[rows,sortKey,sortDir]);
const top3 = sorted.slice(0,3);
const leader = (key) => { let best=null; sorted.forEach(r=>{if(!best||N(r[key])>N(best[key]))best=r;}); return best; };
const ppgL=leader("ppg"),apgL=leader("apg"),rpgL=leader("rpg"),spgL=leader("spg"),tsL=leader("ts_pct"),atL=leader("ast_tov"),f3L=leader("fg3_pct"),ftL=leader("ft_pct");
return(
<div style={{padding:12,fontFamily:"Consolas,monospace",fontSize:12,lineHeight:"18px"}}>
<div style={{color:S.cmt}}>/* Best Point Guard Analysis - 2025-26 NBA Season */</div>
<div style={{marginTop:6}}>Command(s) completed successfully.</div>
<div style={{color:S.muted}}>({sorted.length} rows affected)</div>
{sorted.length>0&&<div style={{marginTop:10,color:S.cmt}}>
-- 🏆 Top 3 by {sortKey.toUpperCase()}:<br/>
{top3.map((r,i)=><span key={i}>-- {i+1}. {r.player_name} ({r.team_id}): {N(r[sortKey])}<br/></span>)}
<br/>
{ppgL&&<>-- 📊 {ppgL.player_name} leads PPG ({N(ppgL.ppg)})<br/></>}
{tsL&&<>-- 📊 {tsL.player_name} leads TS% ({N(tsL.ts_pct)})<br/></>}
{apgL&&<>-- 📊 {apgL.player_name} leads APG ({N(apgL.apg)})<br/></>}
{atL&&<>-- 📊 {atL.player_name} best AST/TO ({N(atL.ast_tov)})<br/></>}
{rpgL&&<>-- 📊 {rpgL.player_name} leads RPG ({N(rpgL.rpg)})<br/></>}
{spgL&&<>-- 📊 {spgL.player_name} leads SPG ({N(spgL.spg)})<br/></>}
{ftL&&<>-- 📊 {ftL.player_name} leads FT% ({N(ftL.ft_pct)})<br/></>}
{f3L&&<>-- 📊 {f3L.player_name} leads 3P% ({N(f3L.fg3_pct)})<br/></>}
</div>}
<div style={{marginTop:10}}>Completion time: {new Date().toISOString().replace('Z','')}</div>
</div>
);
}
function TableMessages({tableName}){
const {data} = useSQLQuery(`SELECT COUNT(*) as cnt FROM "${DB}"."main"."${tableName}"`);
const rows = Array.isArray(data) ? data : [];
const cnt = rows.length>0 ? N(rows[0].cnt) : "...";
return(
<div style={{padding:12,fontFamily:"Consolas,monospace",fontSize:12,lineHeight:"18px"}}>
<div style={{color:S.cmt}}>/* SELECT * FROM "{DB}"."main"."{tableName}" LIMIT 100 */</div>
<div style={{marginTop:6}}>Command(s) completed successfully.</div>
<div style={{color:S.muted}}>(showing up to 100 of {cnt} total rows)</div>
<div style={{marginTop:10}}>Completion time: {new Date().toISOString().replace('Z','')}</div>
</div>
);
}
function StatusRowCount({tab}){
const pgQ = useSQLQuery(`
SELECT COUNT(*) as cnt FROM (
SELECT b.player_name FROM "${DB}"."main"."box_scores" b
JOIN "${DB}"."main"."schedule" s ON b.game_id = s.game_id
WHERE b.period='FullGame' AND s.game_date >= '2025-10-20' AND b.player_name IN (${PG_LIST})
GROUP BY b.player_name, b.team_id HAVING COUNT(*) >= 20
) sub
`, {enabled: tab?.type==="pg"});
const tblQ = useSQLQuery(
`SELECT COUNT(*) as cnt FROM "${DB}"."main"."${tab?.tbl || 'box_scores'}" LIMIT 1`,
{enabled: tab?.type==="tbl"}
);
if(tab?.type==="custom") return <span>LIMIT 100</span>;
const q = tab?.type==="pg" ? pgQ : tblQ;
const rows = Array.isArray(q.data) ? q.data : [];
const cnt = rows.length>0 ? N(rows[0].cnt) : "...";
return <span>{tab?.type==="pg" ? `${cnt} players` : `${Math.min(100,cnt)} of ${cnt} rows`}</span>;
}
export default function App(){
const [sk,setSk]=useState("ppg");
const [sd,setSd]=useState("desc");
const [selR,setSelR]=useState(0);
const [aTab,setATab]=useState(0);
const [rTab,setRTab]=useState("results");
const [tabs,setTabs]=useState([{id:1,name:"SQLQuery1.sql",type:"pg",tbl:null}]);
const [ctxMenu,setCtxMenu]=useState(null);
// execution timer
const [execMs,setExecMs]=useState(0);
const [executing,setExecuting]=useState(false);
const timerRef=useRef(null);
const startRef=useRef(null);
const onExecuting=useCallback((loading)=>{
if(loading){
startRef.current=Date.now();
setExecMs(0);
setExecuting(true);
if(timerRef.current)clearInterval(timerRef.current);
timerRef.current=setInterval(()=>setExecMs(Date.now()-startRef.current),100);
} else {
if(timerRef.current){clearInterval(timerRef.current);timerRef.current=null;}
if(startRef.current)setExecMs(Date.now()-startRef.current);
setExecuting(false);
}
},[]);
useEffect(()=>()=>{if(timerRef.current)clearInterval(timerRef.current);},[]);
const openTbl=useCallback((tbl)=>{
const ex=tabs.findIndex(t=>t.type==="tbl"&&t.tbl===tbl);
if(ex>=0){setATab(ex);return;}
const nid=Math.max(...tabs.map(t=>t.id))+1;
setTabs(p=>[...p,{id:nid,name:`SQLQuery${nid+1}.sql`,type:"tbl",tbl}]);
setATab(tabs.length);setRTab("results");
},[tabs]);
const closeTab=(i,e)=>{e.stopPropagation();if(tabs.length<=1)return;const next=tabs.filter((_,j)=>j!==i);setTabs(next);if(aTab>=next.length)setATab(next.length-1);else if(aTab>i)setATab(aTab-1);};
const openCustom=useCallback(()=>{
const nid=Math.max(...tabs.map(t=>t.id))+1;
setTabs(p=>[...p,{id:nid,name:`SQLQuery${nid+1}.sql`,type:"custom",tbl:null,sql:`SELECT *\nFROM "${DB}"."main"."box_scores"\nWHERE period = 'FullGame'`,execSql:null}]);
setATab(tabs.length);setRTab("results");
},[tabs]);
const updateTabSql=useCallback((sql)=>{
setTabs(p=>p.map((t,i)=>i===aTab?{...t,sql}:t));
},[aTab]);
const executeCustom=useCallback(()=>{
const t=tabs[aTab];
if(t?.type!=="custom"||!t.sql?.trim())return;
const trimmed=t.sql.trim().replace(/;\s*$/,'');
const wrapped=`SELECT * FROM (${trimmed}) AS _q LIMIT 100`;
setTabs(p=>p.map((tab,i)=>i===aTab?{...tab,execSql:wrapped}:tab));
setRTab("results");
},[tabs,aTab]);
const ctxAction=useCallback((action,tbl)=>{
if(action==="top100")openTbl(tbl);
},[openTbl]);
const tab=tabs[aTab];
const clickH=(k)=>{if(k==="idx")return;if(sk===k)setSd(d=>d==="desc"?"asc":"desc");else{setSk(k);setSd("desc");}};
return(
<div style={{fontFamily:"Segoe UI,Tahoma,sans-serif",fontSize:12,color:S.text,background:S.bg,height:"100vh",display:"flex",flexDirection:"column",overflow:"hidden",userSelect:"none"}}
onContextMenu={(e)=>{if(!e.defaultPrevented)e.preventDefault();}}>
{ctxMenu&&<ContextMenu x={ctxMenu.x} y={ctxMenu.y} tbl={ctxMenu.tbl} onAction={ctxAction} onClose={()=>setCtxMenu(null)}/>}
<div style={{background:"#2D2D30",color:"#CCC",padding:"4px 12px",fontSize:11,display:"flex",justifyContent:"space-between"}}>
<span>{tab?.name} - NBA_Box_Scores.master (MotherDuck\Jacob)* - <b style={{color:"#FFF"}}>Microsoft SQL Server Management Studio</b></span>
<span style={{display:"flex",gap:12,cursor:"pointer"}}>─ □ ✕</span>
</div>
<div style={{background:S.menuBg,borderBottom:`1px solid ${S.menuBdr}`,padding:"2px 8px",display:"flex",gap:16,fontSize:12}}>
{["File","Edit","View","Query","Project","Debug","Tools","Window","Help"].map(m=><span key={m} style={{padding:"2px 4px",cursor:"pointer"}}>{m}</span>)}
</div>
<div style={{background:S.toolbar,borderBottom:`1px solid ${S.menuBdr}`,padding:"3px 8px",display:"flex",alignItems:"center",gap:8}}>
<span style={{fontWeight:"bold",fontSize:11}}>🗄️ nba_box_scores</span>
<span onClick={openCustom} style={{background:S.white,border:`1px solid ${S.gridBdr}`,padding:"2px 8px",borderRadius:2,cursor:"pointer",fontSize:11}}>📝 New Query</span>
<span onClick={tab?.type==="custom"?executeCustom:undefined} style={{marginLeft:4,background:"#3C8A3F",color:"#FFF",padding:"2px 10px",borderRadius:2,cursor:tab?.type==="custom"?"pointer":"default",fontWeight:"bold",fontSize:11,opacity:tab?.type==="custom"?1:0.5}}>▶ Execute</span>
{tab?.type==="custom"&&<span style={{fontSize:10,color:S.muted}}>Ctrl+Enter</span>}
{tab?.type==="pg"&&<>
<span style={{marginLeft:16,color:S.muted,fontSize:11}}>Sort by:</span>
<select value={sk} onChange={e=>setSk(e.target.value)} style={{fontSize:11,padding:"1px 4px",border:`1px solid ${S.gridBdr}`,background:"#FFF",fontFamily:"Segoe UI"}}>{SORTS.map(o=><option key={o.k} value={o.k}>{o.l}</option>)}</select>
<span onClick={()=>setSd(d=>d==="desc"?"asc":"desc")} style={{cursor:"pointer",fontSize:13,fontWeight:"bold"}}>{sd==="desc"?"↓":"↑"}</span>
</>}
</div>
<div style={{flex:1,display:"flex",overflow:"hidden"}}>
<div style={{width:215,background:S.white,borderRight:`1px solid ${S.gridBdr}`,display:"flex",flexDirection:"column",flexShrink:0}}>
<div style={{background:S.toolbar,padding:"3px 8px",fontWeight:"bold",fontSize:11,borderBottom:`1px solid ${S.menuBdr}`}}>Object Explorer</div>
<div style={{overflow:"auto",flex:1}}><Tree onOpen={openTbl} onCtxMenu={(x,y,tbl)=>setCtxMenu({x,y,tbl})}/></div>
</div>
<div style={{flex:1,display:"flex",flexDirection:"column",overflow:"hidden"}}>
<div style={{background:S.menuBg,display:"flex",borderBottom:`1px solid ${S.gridBdr}`,overflow:"auto",flexShrink:0}}>
{tabs.map((t,i)=><div key={t.id} onClick={()=>{setATab(i);setRTab("results");}} style={{
background:aTab===i?S.tabOn:S.tabOff,padding:"4px 8px",fontSize:11,display:"flex",alignItems:"center",gap:4,cursor:"pointer",whiteSpace:"nowrap",flexShrink:0,
borderRight:`1px solid ${S.gridBdr}`,borderTop:aTab===i?`2px solid ${S.bar}`:"2px solid transparent"}}>
<span style={{fontSize:10}}>📄</span><span>{t.name}</span>
{t.type==="tbl"&&<span style={{fontSize:9,color:S.muted}}>({t.tbl})</span>}
{t.type==="custom"&&<span style={{fontSize:9,color:S.muted}}>(query)</span>}
{tabs.length>1&&<span onClick={e=>closeTab(i,e)} style={{color:S.muted,cursor:"pointer",marginLeft:4,fontSize:10}}>✕</span>}
</div>)}
</div>
{tab?.type==="custom"
?<SqlEditor value={tab.sql||""} onChange={updateTabSql} onExecute={executeCustom}/>
:<Sql lines={tab?.type==="pg"?pgSql(sk,sd):tblSql(tab?.tbl||"")}/>}
<div style={{background:S.menuBg,display:"flex",borderBottom:`1px solid ${S.gridBdr}`,flexShrink:0}}>
{["results","messages"].map(r=><div key={r} onClick={()=>setRTab(r)} style={{
background:rTab===r?S.tabOn:S.tabOff,padding:"3px 12px",fontSize:11,cursor:"pointer",
borderRight:`1px solid ${S.gridBdr}`,borderTop:rTab===r?`2px solid ${S.bar}`:"2px solid transparent"}}>
{r==="results"?"📊 Results":"💬 Messages"}</div>)}
</div>
<div style={{flex:1,overflow:"auto",background:S.white}}>
{tab?.type==="pg" ? (
rTab==="messages" ? <PGMessages sortKey={sk} sortDir={sd}/> : <PGAnalysis sortKey={sk} sortDir={sd} onHeaderClick={clickH} selRow={selR} onRowClick={setSelR} onExecuting={onExecuting}/>
) : tab?.type==="custom" ? (
rTab==="messages" ? <CustomMessages sql={tab.execSql} rows="≤100"/> : <CustomResults sql={tab.execSql} onExecuting={onExecuting}/>
) : (
rTab==="messages" ? <TableMessages tableName={tab?.tbl}/> : <TablePreview tableName={tab?.tbl} onExecuting={onExecuting}/>
)}
</div>
</div>
</div>
<div style={{background:S.bar,color:"#FFF",padding:"2px 12px",display:"flex",justifyContent:"space-between",fontSize:11,flexShrink:0}}>
<div style={{display:"flex",gap:20}}><span>{executing?"Executing query...":"Ready"}</span><span>DuckDB (MotherDuck)</span><span>MotherDuck\Jacob</span><span>nba_box_scores</span></div>
<div style={{display:"flex",gap:20}}><span>{fmtTimer(execMs)}</span><StatusRowCount tab={tab}/></div>
</div>
</div>
);
}
$dive$,
description = 'Interacting with NBA data as if it is in SQL Server Management Studio',
api_version = 1
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment