Box-score querychat

What it does

• Six steps from a data frame to a handed-over app, in R or Python, with the data description as the step that gets the time.
• A schema template that states every column's meaning, unit and range, plus the rules the data cannot express (what playoffs means, when to exclude zero minutes, that the rows are player-games).
• Guardrails to add before handover: a row limit on aggregations, columns the model may not select, and what the table is not.
• Four checks that say whether the build is done, including that the same question twice gives the same SQL.

How to use it

Put the file in your project's skills folder (for Claude Code, .claude/skills/box-score-querychat/SKILL.md) or hand it to whoever is building the app and follow the steps in order.

Needs: querychat (R or Python), DuckDB comes with it. License: MIT. Written from the method the session teaches; the instructor's own materials come with the recording.

The session it came from

Ask the Box Score Anything. The skill is the method from that session, packaged so you can use it before you have watched it; the session is where the reasoning lives. Every skill in the library names its session the same way.

The file

SKILL.md · 106 lines

---
name: box-score-querychat
description: Put a natural-language question box over a sports table with querychat (R or Python), so the model writes SQL, the database runs it, and every answer is a query the user can read and re-run. Use when someone wants to "ask the data a question" without the data ever leaving the machine.
---

# Box-score querychat

A skill for a coding agent (Claude Code, Positron Assistant, or a person following along). It builds the pattern
from the AthlyticZ session "Ask the Box Score Anything" (Nic Crane): the model turns a question into SQL, DuckDB runs the
SQL against the data, and the result comes back as a table the user can see. The model never receives the rows for a
filter, only the schema; for an aggregation it receives the result of the query it wrote, and that query is shown.

## When to use it

- A stakeholder wants answers from a table and does not write SQL.
- The data is one table (or one view) you can hold in a data frame or a DuckDB file.
- Reproducibility matters: every answer must be a query someone can run again.

Do not use it for data that must never reach a model provider at all, unless the provider runs locally: an
aggregation returns its result to the model so it can write the sentence.

## Steps

1. **Load the table once.** A data frame is enough; querychat uses DuckDB underneath.
2. **Write the data description** (the step people skip, and the one the session spends longest on). Save it as
   `schema.md` next to the app and read it in. For every column: name, meaning, unit, type, and the range or the
   allowed values. Then the business rules the data cannot express. Use the template below.
3. **Create the querychat object** with the table, a table name, a greeting, and the data description.
4. **Run the app**, ask one filter question and one aggregation question, and read the SQL it wrote for each.
5. **Add guardrails**: a row limit on aggregations, a list of columns the model may not select, and a sentence in the
   description saying what the table is not (for example: "player-game rows, not season totals").
6. **Hand it over with three test questions** and the SQL each one should produce.

## The data description template

```markdown
# <table name>: one row per <unit of observation>

Columns
- game_id (text): the league's game identifier. Many rows share one game_id.
- season (integer): the season year, 2020 to 2024.
- season_type (integer): 2 = regular season, 3 = playoffs. There is no 1.
- athlete_display_name (text): the player as the league prints the name.
- team_name (text): the team the player appeared for in that game.
- minutes (numeric): minutes played in that game. 0 means dressed, did not play.
- points, rebounds, assists (integer): per game, for that player.

Rules the data cannot express
- "Playoff" means season_type = 3. "Regular season" means season_type = 2.
- Rates (points per minute) divide by minutes; exclude rows where minutes = 0.
- "Who led" questions return the top 10 unless the user asks for a number.
- This table is player-game rows. Season totals must be computed, never assumed to exist.
```

## R

```r
library(shiny); library(querychat)

box <- arrow::read_parquet("wnba_box.parquet")           # one row per player per game

qc <- querychat_init(
  df = box,
  table_name = "box",
  greeting = "Ask about any WNBA player box score, 2020 to 2024.",
  data_description = paste(readLines("schema.md"), collapse = "\n")
)

ui <- page_sidebar(sidebar = querychat_sidebar("chat"), tableOutput("rows"))
server <- function(input, output, session) {
  chat <- querychat_server("chat", qc)
  output$rows <- renderTable(head(chat$df(), 25))          # the SQL the model wrote, run on your data
}
shinyApp(ui, server)
```

## Python

```python
from shiny import App, render, ui
import querychat as qc
import pandas as pd

box = pd.read_parquet("wnba_box.parquet")
chat = qc.init(box, "box", greeting="Ask about any WNBA player box score, 2020 to 2024.",
               data_description=open("schema.md").read())

app_ui = ui.page_sidebar(qc.sidebar("chat"), ui.output_data_frame("rows"))

def server(input, output, session):
    q = qc.server("chat", chat)
    @render.data_frame
    def rows():
        return q.df().head(25)

app = App(app_ui, server)
```

## Checks before you call it done

- A filter question ("only 2024 playoff games") changes the table and the SQL shows the `WHERE` clause you expect.
- An aggregation question ("who led in points per minute") produces SQL with the `minutes > 0` guard from the rules.
- The same question twice gives the same SQL, or a difference you can explain.
- Nothing in the description names a column that is not in the table.

Written for athlyticz.com/skills from the method the session teaches; the session's own app and notes come with the recording.