---
name: plan-query
description: Stage 3 of the data-analyst pipeline. Decomposes the user's question and the selected tables into a structured plan (TABLES / JOINS / FILTERS / AGGREGATION / EXPECTED_SHAPE / RATIONALE) before any SQL is written. This chain-of-thought separation materially improves accuracy on multi-table or multi-condition questions. Use this skill after select-tables and before generate-sql.
---

# Plan Query — Stage 3

Think before you type. Produce a SHORT structured plan that the SQL generator
follows. This is pure reasoning — no script call.

## Required output format

Plain text, exactly 6 lines, no commentary:

```
TABLES: <comma-separated table names>
JOINS: <one-line description of joins, or "none">
FILTERS: <plain-English filters the user implied>
AGGREGATION: <e.g. "count", "sum(price)", "group by vertical", or "none">
EXPECTED_SHAPE: <e.g. "single number", "5 rows × 2 cols", "list of names">
RATIONALE: <one short sentence>
```

## Inputs you already have

- The user's question
- The list of tables chosen in stage 2
- The fetched schema (DDL text from `scripts/get_schema.py`)
- The per-table metadata file(s) `metadata/tables/<table>.yaml` for chosen
  tables — `code_map` / `pattern_map` blocks tell you the actual code values
  to use in FILTERS (e.g. verified = `verify_status = 9`, not 1).
- Time-window phrases from `data_dictionary.json` (this week / this month / …)

## What "good planning" looks like

**Question:** *"Average sale price for hot leads vs warm leads"*

```
TABLES: sts_dsr_sales
JOINS: none
FILTERS: bm_prospect_status IN ('hot', 'warm')
AGGREGATION: avg(sts_dsr_sales_price), group by bm_prospect_status
EXPECTED_SHAPE: 2 rows × 2 cols (status, avg_price)
RATIONALE: Direct aggregation on a single fact table with a categorical split.
```

**Question:** *"Top 5 reps by name and their calls this week"*

```
TABLES: sts_dsr_call, employee
JOINS: sts_dsr_call.sts_dsr_call_by = employee.employeeid
FILTERS: sts_dsr_call_entrydate >= NOW() - INTERVAL '7 days'
AGGREGATION: count(*) per employeename, order by count DESC, LIMIT 5
EXPECTED_SHAPE: 5 rows × 2 cols (employeename, call_count)
RATIONALE: Per-rep rollup over the last 7 days; join needed to surface names.
```

## Rules

- Keep it to 6 lines. The SQL generator pays attention to the structure, not the prose.
- Use real column names from the schema you fetched — not guesses.
- If you're unsure about a filter / join, write your best guess and note it in RATIONALE; the executor will tell us if we're wrong via stage 5's heal loop.

## Hand-off

Pass the plan text verbatim to stage 4 (generate-sql).