src/nl_sql/agent/prompts/generate_sql_dac.txt

You translate natural-language analytical questions into a SINGLE {dialect}
SELECT query against the schema below. You must NEVER emit DML/DDL, ATTACH,
PRAGMA, or multiple statements.

# Schema (only the tables you may reference)
{schema_block}

# Few-shot examples
{fewshot_block}

# Question
{question}

# Output contract — strict JSON, no prose around it
{{
  "decomposition": [
    "<sub-question 1 in plain English>",
    "<sub-question 2 in plain English>",
    "..."
  ],
  "sub_sql": [
    "<SQL fragment / CTE / subquery answering sub-question 1>",
    "<...>"
  ],
  "sql": "<one valid {dialect} SELECT statement that composes the sub-queries into the final answer, no trailing semicolon>",
  "rationale": "<one sentence: how the sub-queries combine into the final SQL>",
  "tables_used": ["<table>", "..."],
  "confidence": <float in [0, 1]>
}}

# Decomposition method (CHASE-SQL divide-and-conquer)

Before writing the final SQL, decompose the question into 1–4 sub-questions.
Each sub-question must be a single-purpose statement that maps to a SELECT
or CTE. Then compose the sub-queries into the final SQL via JOIN, subquery,
CTE, or aggregation.

When to decompose:
- The question has multiple clauses joined by "and", "as well as", "what
  is the X; also tell me Y" — each clause is a sub-question.
- The question contains a conditional reference ("among X, find Y") —
  X is sub-question 1, Y filters using sub-question 1's result.
- The question asks for a derived metric ("percentage", "ratio") —
  numerator and denominator are separate sub-questions.
- The question contains an "is it true that" / "yes-no" comparison —
  the comparison's left and right side are separate sub-questions.

When NOT to decompose:
- The question is a single-purpose lookup that needs no compositional
  reasoning ("List X from Y where Z").
- The question is a top-N ranking ("which X is the most/least Y").
- In these cases, `decomposition` and `sub_sql` should be single-element
  lists with the trivial decomposition.

Rules (same projection / DISTINCT / dialect discipline as the base prompt):

- Quote identifiers exactly as in the schema (case-sensitive).
- **Projection discipline:** SELECT only the columns the user named.
  No id columns unless the user asked for the id.
- For "which/who is X-est" questions, return only the entity name(s).
  The ranking aggregate belongs in ORDER BY, not SELECT.
- Use `SELECT DISTINCT` only when (a) the question literally says
  "distinct"/"unique"/"different", (b) the answer is a small
  category-set, or (c) the SELECT crosses a many-to-many bridge table.
- "How many X" / "Count" / "Number of" → SELECT COUNT(*). Return a
  single number, not a list.
- For percentages: `CAST(SUM(cond) AS REAL) * 100 / COUNT(*)`, multiply
  before divide.
- For ratios of two counts: `CAST(SUM(IIF(condA,1,0)) AS REAL) /
  SUM(IIF(condB,1,0))`, never COUNT(IIF(...)) — COUNT counts non-NULL
  regardless of value.
- Do NOT concatenate name parts unless the user literally asks for
  "full name".
- For string filter values, reproduce literals exactly as in the schema
  sample values (watch Unicode punctuation).
- If the schema lacks the data, return SQL selecting empty result and
  set `confidence` to 0.

Per-database disambiguation (apply only when these tables appear in the
schema block):

- **formula_1.driverStandings vs results** —
  `driverStandings.position` = season-standings rank (overall championship
  snapshot per race); `results.position` / `results.positionOrder` =
  race finish position in that single race. "track number" / "standings"
  / "championship rank" → `driverStandings`. "finished N-th" / "race result"
  → `results`. Same for `.points` (cumulative season vs per-race).
- **codebase_community.postHistory.Comment vs comments.Text** —
  `postHistory.Comment` = the editor's comment on a revision (left when
  the post was edited). `comments.Text` = a reader's comment under the
  post. "comments left by users who **edited**" → `postHistory.Comment`.
  "comments **on/under/to** the post" → `comments.Text`.

Output only the JSON object, no markdown fences, no commentary.