All MicroEvals
You are generating training data for a text-to-SQL model. B...
Create MicroEval

You are generating training data for a text-to-SQL model. B...

Prompt

You are generating training data for a text-to-SQL model. Below is a SQLite database: its schema and ALL of its rows. ```sql CREATE TABLE stations (station_id integer PRIMARY KEY, name text NOT NULL, city text NOT NULL, opened_on date); CREATE TABLE bikes (bike_id integer PRIMARY KEY, station_id integer REFERENCES stations(station_id), model text NOT NULL, type text NOT NULL, is_retired integer NOT NULL DEFAULT 0); CREATE TABLE members (member_id integer PRIMARY KEY, full_name text NOT NULL, plan text NOT NULL, joined_on date); CREATE TABLE rentals (rental_id integer PRIMARY KEY, bike_id integer NOT NULL REFERENCES bikes(bike_id), member_id integer NOT NULL REFERENCES members(member_id), start_station_id integer NOT NULL REFERENCES stations(station_id), end_station_id integer REFERENCES stations(station_id), started_at text NOT NULL, ended_at text, price_cents integer); You are generating training data for a text-to-SQL model. Below is a SQLite database: its schema and ALL of its rows. ```sql CREATE TABLE stations (station_id integer PRIMARY KEY, name text NOT NULL, city text NOT NULL, opened_on date); CREATE TABLE bikes (bike_id integer PRIMARY KEY, station_id integer REFERENCES stations(station_id), model text NOT NULL, type text NOT NULL, is_retired integer NOT NULL DEFAULT 0); CREATE TABLE members (member_id integer PRIMARY KEY, full_name text NOT NULL, plan text NOT NULL, joined_on date); CREATE TABLE rentals (rental_id integer PRIMARY KEY, bike_id integer NOT NULL REFERENCES bikes(bike_id), member_id integer NOT NULL REFERENCES members(member_id), start_station_id integer NOT NULL REFERENCES stations(station_id), end_station_id integer REFERENCES stations(station_id), started_at text NOT NULL, ended_at text, price_cents integer); INSERT INTO stations VALUES (1,'Harbor Point','Portland','2021-04-01'),(2,'Old Mill','Portland','2022-06-15'),(3,'Union Sq','Seattle','2021-09-10'),(4,'Lakeview','Seattle','2023-03-02'); INSERT INTO bikes VALUES (101,1,'Trek FX 2','city',0),(102,1,'Specialized Turbo Vado','electric',0),(103,2,'Cannondale Synapse','road',0),(104,2,'Trek FX 2','city',1),(105,3,'Rad City 5','electric',0),(106,3,'Giant Escape 3','city',0),(107,4,'Specialized Turbo Vado','electric',0),(108,4,'Cannondale Synapse','road',0); INSERT INTO members VALUES (1,'Ana Ruiz','annual','2022-01-12'),(2,'Ben Okafor','monthly','2023-05-03'),(3,'Chloe Martin','pay_as_you_go','2024-02-20'),(4,'Dev Patel','annual','2021-11-30'),(5,'Emma Svensson','monthly','2024-07-08'),(6,'Farid Haddad','pay_as_you_go','2024-09-15'); INSERT INTO rentals VALUES (1,101,1,1,2,'2024-10-01 08:05','2024-10-01 08:40',350),(2,102,2,1,1,'2024-10-01 17:20','2024-10-01 18:05',900),(3,105,4,3,4,'2024-10-02 07:50','2024-10-02 08:30',800),(4,103,3,2,2,'2024-10-03 12:00','2024-10-03 14:10',1300),(5,106,5,3,3,'2024-10-03 18:15','2024-10-03 18:45',300),(6,107,1,4,3,'2024-10-05 09:30','2024-10-05 10:20',1000),(7,101,6,1,1,'2024-10-06 11:00','2024-10-06 11:25',250),(8,108,4,4,4,'2024-10-07 06:40','2024-10-07 08:00',900),(9,102,2,1,2,'2024-10-08 17:10','2024-10-08 17:55',900),(10,105,5,3,NULL,'2024-10-09 19:00',NULL,NULL),(11,106,3,3,4,'2024-10-10 08:00','2024-10-10 08:35',350),(12,107,4,4,4,'2024-10-11 16:45','2024-10-11 17:50',1300),(13,103,1,2,1,'2024-10-12 10:10','2024-10-12 11:40',1100),(14,101,2,1,1,'2024-10-12 13:00','2024-10-12 13:20',200),(15,102,6,1,3,'2024-10-13 15:30','2024-10-13 16:45',1500); ``` Write 4 SQLite queries a developer on this app might really need, one per level: 1. simple: one table, a filter or a sort 2. moderate: a join and an aggregate 3. complex: several joins plus GROUP BY/HAVING, a subquery, or a LEFT JOIN that keeps unmatched rows 4. very complex: a CTE or window function Rules: - Every query must run on the data above and return at least one row. - Use only columns that exist. Pay attention to NULLs (rental 10 has not ended yet). - For each query, also write: - "question": how a developer would ask for it in one natural sentence - "terse": the same request as a short code-comment style phrase, e.g. "top 5 members by spend" - "description": one sentence describing exactly what the query returns, complete enough that someone could rewrite the query from it Return ONLY a JSON array of 4 objects with keys: level, sql, question, terse, description.

Drag to resize