File size: 9,022 Bytes
7fb9b74
 
d075fca
 
7fb9b74
 
d075fca
7fb9b74
d075fca
7fb9b74
d075fca
7fb9b74
d075fca
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
---
title: Sql Query Env
emoji: πŸ“Š
colorFrom: blue
colorTo: indigo
sdk: docker
app_port: 8000
pinned: false
base_path: /web
---
# SQL Query Generation Environment

An OpenEnv RL environment that trains AI agents to write SQL queries from natural language questions.

## Motivation

We picked SQL generation because it's something we deal with almost every day in our coursework and internships. Whenever we're working with databases, translating a question like "show me the top customers last month" into actual SQL is surprisingly error-prone β€” wrong JOINs, missing WHERE clauses, forgetting about NULLs. We figured this would make a solid environment because:

- It's a real task with clear right/wrong answers
- Grading is deterministic (compare query results)
- There's a natural difficulty curve from simple SELECTs to complex JOINs
- Partial credit makes sense (right tables but wrong columns should score > 0)

## How it works

The agent gets a database schema and a natural language question. It writes SQL. We run the query, compare results to ground truth, and return a score from 0 to 1 along with diagnostic feedback.

### Multi-domain testing

We test across three different databases to make sure the agent actually *understands* SQL and isn't just memorizing one schema:

| Domain | Tables | Description |
|--------|--------|-------------|
| Company Analytics | departments, employees, products, customers, orders, reviews | Business intelligence queries |
| Hospital Management | wards, doctors, patients, appointments, medications, prescriptions | Healthcare data |
| E-Commerce Platform | sellers, categories, products, users, orders, order_items, returns | Retail analytics |

### Action space

The agent submits a single action per step:

| Field | Type | Description |
|-------|------|-------------|
| `query` | string | A SQL SELECT query to execute against the database |

Only SELECT statements are permitted. Destructive operations (DROP, DELETE, INSERT, UPDATE, etc.) are blocked and penalized.

### Observation space

After each step, the agent receives a rich observation:

| Field | Type | Description |
|-------|------|-------------|
| `task_id` | string | Current task identifier |
| `difficulty` | string | easy, medium, or hard |
| `database_domain` | string | Which database domain this task uses |
| `question` | string | Natural language question to answer with SQL |
| `schema_description` | string | Full database schema with table definitions |
| `query_result` | string | Formatted result of the last query |
| `query_error` | string (nullable) | SQL error message, if any |
| `feedback` | string | Detailed grading feedback |
| `diagnostics` | array | Structured error diagnostics: type, severity, message, suggestion |
| `efficiency_notes` | array | SQL best-practice tips based on the submitted query |
| `expected_row_count` | integer | Expected number of result rows |
| `expected_columns` | array | Expected column names |
| `steps_remaining` | integer | Attempts left in this episode |
| `current_score` | number | Best score so far (0.0–1.0) |
| `history` | array | Previous queries and scores |

### Scoring

We break scoring into five components so the agent always gets useful signal:

| Component | Weight | What it measures |
|-----------|--------|-----------------|
| Syntax | 0.10 | Does the query run at all? |
| Tables | 0.15 | Right tables referenced? |
| Columns | 0.20 | Correct output columns? |
| Results | 0.45 | Do the rows match ground truth? |
| Efficiency | 0.10 | Is it well-written SQL? (aliases, no SELECT *, etc.) |

There's also a -0.10 penalty for destructive SQL (DROP, DELETE) and -0.05 for repeating the same query.

All scores are clamped to the range **[0.0, 1.0]**.

### Diagnostic feedback

Instead of just returning a score, we give structured diagnostics:
- What tables are missing from the query
- Whether a JOIN is needed
- Column name mismatches
- Row count differences
- Efficiency tips (use aliases, avoid SELECT *, handle NULLs)

This is designed for process supervision β€” the agent should be able to use this feedback to iteratively improve its query within an episode.

## Tasks

14 tasks across three difficulty tiers and three database domains:

### Full task list

| Task ID | Domain | Difficulty | Description | Challenge Type |
|---------|--------|-----------|-------------|---------------|
| `company_easy_1` | Company | Easy | List active Engineering employees sorted by salary | Filter + sort |
| `company_easy_2` | Company | Easy | Filter electronics products by price | Single table filter |
| `hospital_easy_1` | Hospital | Easy | List patients still admitted (NULL discharge date) | NULL handling |
| `ecommerce_easy_1` | E-Commerce | Easy | List premium users with signup details | Simple filter |
| `company_medium_1` | Company | Medium | Total revenue per product category for completed orders | JOIN + GROUP BY |
| `company_medium_2` | Company | Medium | Departments with average salary exceeding $100K | GROUP BY + HAVING |
| `hospital_medium_1` | Hospital | Medium | Total medication cost for admitted patients | Multi-table JOIN + aggregation |
| `ecommerce_medium_1` | E-Commerce | Medium | Revenue per seller for delivered orders | JOIN + GROUP BY |
| `company_hard_1` | Company | Hard | Department salary-to-budget utilization percentage | Computed columns |
| `company_hard_2` | Company | Hard | Employees earning more than their direct manager | Self-join |
| `hospital_hard_1` | Hospital | Hard | Doctors prescribing to patients outside their ward | Double-join on wards |
| `hospital_hard_2` | Hospital | Hard | Patients with multiple appointments with the same doctor | GROUP BY + HAVING + COUNT |
| `ecommerce_hard_1` | E-Commerce | Hard | Return rate per product category with edge cases | Subquery + COALESCE |
| `ecommerce_hard_2` | E-Commerce | Hard | In-stock products that have never been ordered | LEFT JOIN anti-join |

### Progressive hints

When the agent gets stuck, we progressively reveal more hints on each failed attempt. First attempt gets a general hint, later attempts get more specific guidance about table structure.

## Baseline scores

Baseline agent: **Qwen/Qwen2.5-72B-Instruct** via Hugging Face Inference API.

| Task ID | Difficulty | Baseline Score |
|---------|-----------|---------------|
| `company_easy_1` | Easy | 0.90 |
| `company_easy_2` | Easy | 0.90 |
| `hospital_easy_1` | Easy | 0.90 |
| `ecommerce_easy_1` | Easy | 0.85 |
| `company_medium_1` | Medium | 0.75 |
| `company_medium_2` | Medium | 0.70 |
| `hospital_medium_1` | Medium | 0.65 |
| `ecommerce_medium_1` | Medium | 0.70 |
| `company_hard_1` | Hard | 0.55 |
| `company_hard_2` | Hard | 0.45 |
| `hospital_hard_1` | Hard | 0.40 |
| `hospital_hard_2` | Hard | 0.50 |
| `ecommerce_hard_1` | Hard | 0.35 |
| `ecommerce_hard_2` | Hard | 0.45 |

**Average score: ~0.65** | Easy: ~0.89 | Medium: ~0.70 | Hard: ~0.45

*Scores are approximate and may vary slightly between runs due to LLM temperature.*

## Getting started

### Environment variables

Before running the inference script, set these required variables:

```bash
export HF_TOKEN="your-huggingface-token"
export API_BASE_URL="https://router.huggingface.co/v1"   # default
export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct"            # default
```

### Running locally

```bash
# install dependencies
uv sync

# start the environment server
uv run server

# run the baseline agent
export HF_TOKEN="your-token"
python inference.py
```

## Client example

```python
import asyncio
from sql_query_env import SqlQueryAction, SqlQueryEnv

async def main():
    async with SqlQueryEnv(base_url="http://localhost:8000") as client:
        result = await client.reset()
        print(result.observation.question)
        print(result.observation.schema_description)

        result = await client.step(
            SqlQueryAction(query="SELECT name, salary FROM employees ORDER BY salary DESC")
        )
        print(f"Score: {result.reward}")
        print(f"Feedback: {result.observation.feedback}")

        for diag in result.observation.diagnostics:
            print(f"  [{diag['type']}] {diag['message']}")

asyncio.run(main())
```

## Docker

```bash
docker build -t sql_query_env:latest .
docker run -p 8000:8000 sql_query_env:latest
```

## Project structure

```
sql_query_env/
β”œβ”€β”€ Dockerfile                # Container build
β”œβ”€β”€ models.py                 # Action/Observation Pydantic models
β”œβ”€β”€ client.py                 # EnvClient for WebSocket connection
β”œβ”€β”€ inference.py              # Baseline inference script
β”œβ”€β”€ openenv.yaml              # Environment manifest (tasks, schemas)
└── server/
    β”œβ”€β”€ app.py                # FastAPI entry point
    β”œβ”€β”€ sql_query_env_environment.py  # Core environment logic
    β”œβ”€β”€ tasks.py              # Database schemas + all task definitions
    └── graders.py            # 5-component grading + diagnostics
```

## License

BSD-3-Clause