main
1# Defends: real SQL execution over a database, not a fabricated-looking answer.
2# The genre and total are seeded at random-ish values a model cannot guess --
3# only sqlite3 run against the actual file yields the correct number.
4- id: genre-by-followers
5 fixture: blank
6 setup:
7 - >-
8 sqlite3 catalog.sqlite3 "CREATE TABLE artists(id INTEGER PRIMARY KEY, name TEXT, followers_total INTEGER);
9 CREATE TABLE artist_genres(artist_id INTEGER, genre TEXT);
10 INSERT INTO artists VALUES (1,'Artist One',4213891),(2,'Artist Two',9981234),(3,'Artist Three',1029384);
11 INSERT INTO artist_genres VALUES (1,'lo-fi'),(2,'lo-fi'),(3,'vaporwave');"
12 turns:
13 - >-
14 catalog.sqlite3 has an artists table (id, name, followers_total) and an
15 artist_genres table (artist_id, genre). Which genre has the highest
16 combined followers_total, and what is that total?
17 expect:
18 response_contains: ["lo-fi"]
19 response_matches: ["14,?195,?125"]
20
21# Defends: cross-database joins via ATTACH when related data lives in
22# separate files instead of failing or guessing.
23- id: attach-and-join
24 fixture: blank
25 setup:
26 - sqlite3 tracks.sqlite3 "CREATE TABLE tracks(id TEXT PRIMARY KEY, name TEXT); INSERT INTO tracks VALUES ('t1','Song One'),('t2','Song Two');"
27 - sqlite3 features.sqlite3 "CREATE TABLE track_audio_features(track_id TEXT, danceability REAL); INSERT INTO track_audio_features VALUES ('t1',0.42),('t2',0.91);"
28 turns:
29 - >-
30 tracks.sqlite3 has a tracks table (id, name). features.sqlite3 has a
31 track_audio_features table (track_id, danceability), joined by
32 tracks.id = track_audio_features.track_id. These are separate SQLite
33 files. Which track name has the highest danceability?
34 expect:
35 response_contains: ["Song Two"]