# SCM-SQL YAML schema

The dataset is a single YAML document at `data/pilot_500.yaml` with the
top-level key `pairs`, a list of 500 pair objects.

## Pair object

Two variants — single-turn (levels L1-L5) and multi-turn (level L6).

### Single-turn (L1-L5)

```yaml
- id: L2-014
  level: 2
  domains: [demand, finance]
  nl: "Show total revenue by customer for confirmed sale orders this year."
  gold_sql: |
    SELECT rp.name AS customer,
           SUM(so.amount_total) AS total_revenue
    FROM sale_order so
    JOIN res_partner rp ON rp.id = so.partner_id
    WHERE so.state IN ('sale','done')
      AND so.date_order >= date_trunc('year', NOW())
    GROUP BY rp.id, rp.name
    ORDER BY total_revenue DESC NULLS LAST;
  tags: [join, aggregate, temporal]
```

### Multi-turn (L6)

```yaml
- id: L6-003
  level: 6
  domains: [inventory]
  turns:
    - nl: "Show on-hand quantity by product."
      gold_sql: |
        SELECT pt.name->>'en_US' AS product, SUM(sq.quantity) AS on_hand
        FROM stock_quant sq
        JOIN product_product pp ON pp.id = sq.product_id
        JOIN product_template pt ON pt.id = pp.product_tmpl_id
        GROUP BY pt.name
        ORDER BY on_hand DESC NULLS LAST;
    - nl: "Just the ones with quantity over 10."
      gold_sql: |
        SELECT pt.name->>'en_US' AS product, SUM(sq.quantity) AS on_hand
        FROM stock_quant sq
        JOIN product_product pp ON pp.id = sq.product_id
        JOIN product_template pt ON pt.id = pp.product_tmpl_id
        GROUP BY pt.name
        HAVING SUM(sq.quantity) > 10
        ORDER BY on_hand DESC NULLS LAST;
  tags: [multi_turn, refinement]
```

## Fields

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `id` | string | yes | Unique identifier `L{level}-{seq:03d}` |
| `level` | int (1-6) | yes | Complexity tier |
| `domains` | list[string] | yes | Subset of `[demand, finance, inventory, logistics]` |
| `nl` | string | L1-L5 only | Natural-language question |
| `gold_sql` | string | L1-L5 only | Executable PostgreSQL statement |
| `turns` | list[object] | L6 only | Ordered turns, each `{nl, gold_sql}` |
| `tags` | list[string] | yes | Intent tags — see below |

## Intent tag vocabulary

Non-exhaustive; new tags may appear as the set grows.

| Tag | Meaning |
|-----|---------|
| `aggregate` | Query uses SUM / COUNT / AVG / MIN / MAX |
| `count` | Query returns a scalar count |
| `filter` | Query has a WHERE clause |
| `join` | Query joins two or more tables |
| `nested` | Query has a subquery |
| `ranking` | Query uses ORDER BY + LIMIT |
| `temporal` | Query filters on a date/time column |
| `window` | Query uses window functions (LAG, LEAD, ROW_NUMBER, RANK) |
| `cte` | Query uses WITH … AS |
| `rollup` | Query uses GROUPING SETS / ROLLUP / CUBE |
| `multi_turn` | Pair is a multi-turn dialogue (L6) |
| `refinement` | Later turn refines an earlier turn's SQL |
| `cross_domain` | Query spans 2+ supply-chain domains |
| `currency` | Query involves multi-currency computation |
| `predictive` | Query includes a forward-looking projection |

## Complexity level definitions

| Level | Style | SQL surface exercised |
|-------|-------|------------------------|
| L1 | Single-table filter / aggregate | `SELECT COUNT|SUM|AVG(...) FROM t WHERE ...` |
| L2 | Two-table JOIN + GROUP BY | Standard analytical join |
| L3 | Nested subquery / semi-join / 3-table join | Correlated subquery, IN/EXISTS |
| L4 | Window function | LAG, LEAD, ROW_NUMBER, RANK, PARTITION BY |
| L5 | CTE + rollup | WITH clauses, GROUPING SETS, ROLLUP |
| L6 | Multi-turn refinement | 2-3 turns; each turn's SQL is a derivative of the previous |

## Notes on the target database

All gold SQLs are written for PostgreSQL 16 running the stock Odoo 17
demo database. A few Odoo-specific idioms appear across the set:

- `state IN ('sale', 'done')` — canonical filter for confirmed sale orders.
- `am.move_type = 'out_invoice' AND am.state = 'posted'` — canonical filter for issued invoices.
- `pt.name->>'en_US'` — Odoo stores product names as JSONB translated strings; use `->>` to extract the English string.
- `dimemployee`, `dimsalesterritory`, etc. — none of these appear; those are Contoso schema names from unrelated systems.

## Validation

Every pair in `data/pilot_500.yaml` was executed against a stock
`odoo:17.0` Docker image before being included in the release. The
execute-verify gate rejects any pair whose gold SQL fails to parse
(sqlglot), fails to execute (psycopg), returns zero rows, or returns
only-null values.