企业级Text-to-SQL
Enterprise text-to-SQL
Answer questions with correct SQL over large real schemas and dialects.
20 条本项目编写的提案 · 分类 v1.1 · 新增基准运行 0 次。下方验证方式与部分得分规则是设计，不是执行结果。
- 典型应用
- PostgreSQL · Snowflake · BigQuery · DuckDB
- 小类 GUI 需求标签
CL· CLI 足够
任务提案 · 20
展开查看完整原文与建议检查方式；两种界面均保留提案英文原文。
R03.01.01.01-T01 Count monthly active users with 30-minute sessionization in DuckDB
原始指令
data/events/*.parquet (420M rows: user_id, ts UTC, event) is attached in DuckDB. Write output/q.sql returning, for each month of 2025, active users and average sessions per active user, where a session ends after 30 minutes of inactivity. Save the result to output/answer.csv.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- DuckDB
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader executes output/q.sql on a hidden 10% resample and the full data: result must equal the gold result (12 rows; averages ±0.001). answer.csv must equal the full-data result. Hard-coded values detected by the resample run.
建议部分得分
20% runs; 40% active-user counts; 40% sessions per user
R03.01.01.01-T02 Rank categories by net gross margin over a 200-table retail schema
原始指令
PostgreSQL database 'retail' has 214 tables (docs in docs/erd.pdf). Write output/q.sql for: the top 5 product categories by gross margin in Q3 2025, net of returns and excluding internal test orders, with margin in USD rounded to 2 decimals.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- PostgreSQL · psql
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Execution match vs gold on the live DB and on a hidden perturbed copy (shifted prices, extra returns): same 5 categories in the same order, margins ±0.01. Gold excludes orders.is_test and channel 'internal'.
建议部分得分
20% runs; 30% correct category set; 20% order; 30% margin values
R03.01.01.01-T03 Flatten JSON telemetry in Snowflake to count devices by firmware
原始指令
Snowflake table IOT.RAW.TELEMETRY stores payloads in a VARIANT column with nested arrays of modules. Write output/q.sql returning distinct device count per firmware version for devices that reported a module of type 'lidar' in September 2025.
- 平台
web· 任意系统（网页）lin· Linux- 应用
- Snowflake · SnowSQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Executed on Snowflake: result equals gold (firmware, count) on live data and a hidden variant with extra nesting edge cases (null modules, duplicate payloads). Uses LATERAL FLATTEN or equivalent; no external staging.
建议部分得分
20% runs; 50% counts per firmware; 30% robust on hidden variant
R03.01.01.01-T04 Compute a GA4 purchase funnel in BigQuery
原始指令
Using the GA4 export sample in BigQuery (events_* sharded tables), compute for November 2025 the user-level funnel session_start → view_item → add_to_cart → begin_checkout → purchase, counting users who completed each step in order within the same session, by device category.
- 平台
web· 任意系统（网页）lin· Linux- 应用
- BigQuery · bq CLI
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Execution match vs gold (device × step counts and step conversion rates ±0.0001). Hidden variant with out-of-order events and missing ga_session_id must also match. Query must bill ≤5 GB (dry-run check).
建议部分得分
20% runs within byte budget; 30% session keying via UNNEST(event_params); 30% ordered steps; 20% hidden variant
R03.01.01.01-T05 Answer an order-value question over a cryptic ERP schema
原始指令
The PostgreSQL copy of an SAP-style ERP has 1,240 tables with names like VBAK, VBAP, KNA1, KNVV and German column names, documented only in data dictionary table DD03L. Find the open sales order value (net, in EUR) per customer group for sales org 1000 as of 2025-12-31.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- PostgreSQL · psql
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
long· 长流程- 提案难度
- 5 / 5
建议验证方式
Execution match vs gold: groups and values ±0.01 EUR. Requires joining header/item/customer sales area tables, excluding rejected items and fully delivered orders, and converting currency via TCURR as of date. Hidden perturbed DB guards against hard-coding.
建议部分得分
15% correct tables identified (logged in output/tables.txt); 25% open-item logic; 20% currency conversion; 40% exact values
R03.01.01.01-T06 Translate 15 T-SQL reports to Snowflake SQL
原始指令
queries/tsql/ contains 15 SQL Server reports (TOP, CROSS APPLY, DATEADD, ISNULL, temp tables, PIVOT). Rewrite each as Snowflake SQL in queries/snowflake/ against the migrated schema so results are identical.
- 平台
web· 任意系统（网页）lin· Linux- 应用
- Snowflake · SQL Server
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader runs original queries on SQL Server and the translations on Snowflake over identical data: per-query result equality (ordered where ORDER BY exists, numeric ±1e-6). Per-query credit.
建议部分得分
33% queries 1–5 match; 33% queries 6–10 match; 34% queries 11–15 match (≈6.7 pts per query)
R03.01.01.01-T07 Report revenue by 4-4-5 fiscal week in local store time
原始指令
In PostgreSQL 'retail', report net revenue per fiscal week of FY2025 for each region, where the fiscal calendar is 4-4-5 starting the Sunday nearest 1 Feb (see docs/fiscal.md) and order timestamps (UTC) must be bucketed by each store's local time zone.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- PostgreSQL · psql
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Execution match vs gold (region × fiscal week, 52 or 53 weeks). Hidden test with DST-boundary orders and a 53-week year must match. Calendar must be generated in SQL, not a pasted list (static check for ≥50 literal dates).
建议部分得分
25% fiscal calendar; 30% time-zone bucketing; 45% revenue values
R03.01.01.01-T08 Join orders to the customer segment valid at order time
原始指令
Table dim_customer_scd2 (valid_from, valid_to, is_current) tracks customer segment changes. Write a query giving 2025 revenue by the segment the customer was in at order time, and a second query showing how much revenue would be misattributed if the current segment were used.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- DuckDB
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Both queries executed: first must match gold per segment (±0.01); second must match gold misattribution total. Hidden data with gaps and overlapping validity rows must be handled per docs/scd_rules.md.
建议部分得分
50% point-in-time revenue; 30% misattribution figure; 20% hidden edge cases
R03.01.01.01-T09 Ask before answering an ambiguous 'top customers' request
原始指令
A sales VP asks: 'Who were our top customers last year?' against the 'crm' PostgreSQL DB. The schema supports several readings (revenue vs orders vs margin; calendar vs fiscal year; parent account vs subsidiary). Write output/response.md with clarifying questions, and the query you would run under each reading.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- PostgreSQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP12· 答案 / 决策- 条件
clarify· 需要澄清- 提案难度
- 3 / 5
建议验证方式
Grader checks the response asks about metric, period and account hierarchy (keyword/LLM rubric, 40%), and executes each provided query: each must be correct for its stated interpretation vs gold (60%). Answering with a single unqualified list scores ≤20%.
建议部分得分
40% the three ambiguities raised; 60% interpretation queries correct
R03.01.01.01-T10 Roll up headcount through an org hierarchy with a recursive CTE
原始指令
Table employees(id, manager_id, dept, hired, terminated) in DuckDB holds 48k rows. Return each VP (level 2 under the CEO) with total current headcount in their reporting tree and the tree's maximum depth.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- DuckDB
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Execution match vs gold (VP id, headcount, depth). Hidden variant includes a manager cycle that must not loop (query must finish in <10 s) and terminated managers whose reports still roll up.
建议部分得分
60% headcounts; 20% depth; 20% hidden cycle/terminated handling
R03.01.01.01-T11 Build a weekly retention cohort table with window functions
原始指令
From 'app' PostgreSQL tables signups and sessions, produce a cohort table: signup week (ISO) × weeks since signup 0–12, with the percentage of the cohort active in that week, for cohorts from 2025-W01 to 2025-W40.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- PostgreSQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Execution match vs gold (40×13 cells ±0.01 pp). Must count each user once per week; future weeks null not zero. Hidden perturbed DB.
建议部分得分
20% cohort sizes; 60% retention cells; 20% null handling
R03.01.01.01-T12 Answer a revenue question while handling duplicates and soft deletes
原始指令
In the MySQL 'billing' DB, invoices are sometimes duplicated by retries (same external_id) and soft-deleted (deleted_at). Answer: total collected revenue per plan for H1 2025, counting each external invoice once and excluding deleted or refunded invoices.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- MySQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Execution match vs gold per plan ±0.01. Hidden perturbed copy adds more duplicates and partial refunds. Answer that ignores dedup is off by ≥8%, so it fails.
建议部分得分
20% runs; 40% dedup logic; 20% refund/delete exclusion; 20% values
R03.01.01.01-T13 Rewrite a slow report query to run under two seconds
原始指令
queries/slow_report.sql takes 95 s on the 50M-row 'events' PostgreSQL table (correlated subqueries, OR joins). Rewrite it as output/fast_report.sql returning identical results in under 2 s; you may add at most 2 indexes, recorded in output/indexes.sql.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- PostgreSQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader applies indexes.sql to a fresh copy, runs both queries 3× (warm): results identical (EXCEPT both ways empty) and median runtime <2 s. Index count ≤2; no materialized views.
建议部分得分
40% identical results; 40% runtime target (partial ≤10 s); 20% index budget respected
R03.01.01.01-T14 Count stores near warehouses with PostGIS
原始指令
In PostGIS database 'logistics', for each warehouse return the number of stores within 5 km road-agnostic geodesic distance and the nearest store's id and distance in meters. Geometries are in EPSG:4326.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- PostgreSQL · PostGIS
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Execution match vs gold: counts exact, nearest id exact, distance ±1 m. Planar-degree distances give wrong counts for high-latitude warehouses, so geography casting is required.
建议部分得分
50% counts; 30% nearest store; 20% distances
R03.01.01.01-T15 Convert multi-currency sales with an as-of FX join in SQL Server
原始指令
In SQL Server 'sales', convert every 2025 order to USD using the most recent FX rate on or before the order date (fx_rates has gaps on weekends and holidays) and report monthly USD revenue by country.
- 平台
win· Windowslin· Linux- 应用
- SQL Server · sqlcmd
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Execution match vs gold (country × month ±0.01 USD). Hidden variant with missing rates for a currency over 10 days. Uses an as-of pattern (OUTER APPLY or window) not equality join.
建议部分得分
30% as-of join correctness; 50% monthly values; 20% hidden gap variant
R03.01.01.01-T16 Compute hourly p95 latency per endpoint in ClickHouse
原始指令
ClickHouse table http_logs (1.1B rows) holds request durations. Return per endpoint and hour on 2025-10-01 the request count and exact p95 latency in ms, only for endpoints with ≥1,000 requests that hour.
- 平台
lin· Linuxmac· macOS- 应用
- ClickHouse
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Execution match vs gold: counts exact; p95 within 0.5 ms (quantileExact required; quantile() approximations fail tolerance on seeded heavy-tail endpoints). Threshold filter exact.
建议部分得分
30% counts; 50% p95 values; 20% threshold filter
R03.01.01.01-T17 Answer 25 business questions over an unfamiliar warehouse
原始指令
questions/q01–q25.md each ask a business question about the 'fin_dw' DuckDB warehouse (380 tables, partial docs in docs/). Write output/qNN.sql for each; some questions need metadata exploration, multi-step CTEs or domain conventions described only in docs/.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- DuckDB
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
long· 长流程- 提案难度
- 5 / 5
建议验证方式
Per-question execution match vs gold on the live DB and a perturbed copy (both must match). Score = fraction matched. No external resources; queries must run in ≤60 s each.
建议部分得分
48% q01–q12 (4 pts each); 52% q13–q25 (4 pts each); a question scores only if it matches on both the live DB and the perturbed copy
R03.01.01.01-T18 List the ten most-borrowed books of 2024 in a library DB
原始指令
SQLite file data/library.db holds loans, copies and books. Return the 10 titles with the most loans started in 2024 with their loan counts, breaking ties alphabetically.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- SQLite
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Execution match vs gold (ordered, 10 rows). Loans join through copies to books; hidden perturbed DB.
建议部分得分
30% joins via copies; 50% counts; 20% ordering and tie-break
R03.01.01.01-T19 Answer a question using only the BigQuery web console
原始指令
You have no CLI or service account, only a browser session in BigQuery Studio with read access to dataset 'shared_fin'. Find the question owner's saved query 'churn_base', adapt it to answer churned MRR by plan for Q3 2025, and save your query as 'churn_q3_by_plan' in the project.
- 平台
web· 任意系统（网页）- 应用
- BigQuery Studio
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader fetches the saved query via an admin API, executes it: result equals gold per plan ±0.01. Saved query name exact and created by the agent's user. Agent environment has no gcloud credentials.
建议部分得分
20% found and opened base query; 60% correct result; 20% saved with correct name
R03.01.01.01-T20 Join OLTP Postgres with Parquet files in object storage
原始指令
Customer master data lives in PostgreSQL 'crm'; 2 years of clickstream lives as Parquet in the local MinIO bucket s3://lake/clicks/. Using DuckDB with the postgres and httpfs extensions, compute conversion rate by customer industry and month, writing results to output/conv.parquet.
- 平台
lin· Linuxmac· macOS- 应用
- DuckDB · PostgreSQL · MinIO
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
multiapp· 多应用- 提案难度
- 5 / 5
建议验证方式
Grader reads output/conv.parquet: equals gold (industry × month, rates ±1e-4). Script must be reproducible: grader reruns output/run.sql on a fresh session with perturbed Parquet partitions and matches the new gold.
建议部分得分
20% connects both sources; 30% join keys and dedup; 30% results; 20% reproducible on perturbed data
已有研究映射
以下沿用研究目录中的基准与小类映射，不是逐题标注、执行记录或产品背书。