Commit fdd8382

@@ -0,0 +1,35 @@
+# Defends: real SQL execution over a database, not a fabricated-looking answer.
+# The genre and total are seeded at random-ish values a model cannot guess --
+# only sqlite3 run against the actual file yields the correct number.
+- id: genre-by-followers
+  fixture: blank
+  setup:
+    - >-
+      sqlite3 catalog.sqlite3 "CREATE TABLE artists(id INTEGER PRIMARY KEY, name TEXT, followers_total INTEGER);
+      CREATE TABLE artist_genres(artist_id INTEGER, genre TEXT);
+      INSERT INTO artists VALUES (1,'Artist One',4213891),(2,'Artist Two',9981234),(3,'Artist Three',1029384);
+      INSERT INTO artist_genres VALUES (1,'lo-fi'),(2,'lo-fi'),(3,'vaporwave');"
+  turns:
+    - >-
+      catalog.sqlite3 has an artists table (id, name, followers_total) and an
+      artist_genres table (artist_id, genre). Which genre has the highest
+      combined followers_total, and what is that total?
+  expect:
+    response_contains: ["lo-fi"]
+    response_matches: ["14,?195,?125"]
+
+# Defends: cross-database joins via ATTACH when related data lives in
+# separate files instead of failing or guessing.
+- id: attach-and-join
+  fixture: blank
+  setup:
+    - sqlite3 tracks.sqlite3 "CREATE TABLE tracks(id TEXT PRIMARY KEY, name TEXT); INSERT INTO tracks VALUES ('t1','Song One'),('t2','Song Two');"
+    - 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);"
+  turns:
+    - >-
+      tracks.sqlite3 has a tracks table (id, name). features.sqlite3 has a
+      track_audio_features table (track_id, danceability), joined by
+      tracks.id = track_audio_features.track_id. These are separate SQLite
+      files. Which track name has the highest danceability?
+  expect:
+    response_contains: ["Song Two"]