dvwn/nl2sql-api

1[2  {3    "id": 1,4    "difficulty": "easy",5    "taxonomy": "Selection",6    "question": "List all the artists name in the database.",7    "gold_sql": "SELECT Name FROM Artist;"8  },9  {10    "id": 2,11    "difficulty": "easy",12    "taxonomy": "Aggregation",13    "question": "How many genres are there?",14    "gold_sql": "SELECT COUNT(*) FROM Genre;"15  },16  {17    "id": 3,18    "difficulty": "easy",19    "taxonomy": "Selection, Limit",20    "question": "List the names of the first 5 tracks.",21    "gold_sql": "SELECT Name FROM Track LIMIT 5;"22  },23  {24    "id": 4,25    "difficulty": "easy",26    "taxonomy": "Aggregation, Filtering",27    "question": "Count the number of customers located in the USA.",28    "gold_sql": "SELECT COUNT(*) FROM Customer WHERE Country = 'USA';"29  },30  {31    "id": 5,32    "difficulty": "easy",33    "taxonomy": "Selection, Filtering",34    "question": "Find all invoices for the customer with ID 1.",35    "gold_sql": "SELECT * FROM Invoice WHERE CustomerId = 1;"36  },37  {38    "id": 6,39    "difficulty": "medium",40    "taxonomy": "Simple Join",41    "question": "List each album title along with the artist's name.",42    "gold_sql": "SELECT Album.Title, Artist.Name FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId;"43  },44  {45    "id": 7,46    "difficulty": "medium",47    "taxonomy": "Simple Join, Filtering, Aggregation",48    "question": "How many tracks belong to the 'Rock' genre?",49    "gold_sql": "SELECT COUNT(*) FROM Track JOIN Genre ON Track.GenreId = Genre.GenreId WHERE Genre.Name = 'Rock';"50  },51  {52    "id": 8,53    "difficulty": "medium",54    "taxonomy": "Aggregation, Grouping",55    "question": "Show the total revenue generated from each country.",56    "gold_sql": "SELECT BillingCountry, SUM(Total) FROM Invoice GROUP BY BillingCountry;"57  },58  {59    "id": 9,60    "difficulty": "medium",61    "taxonomy": "Multi-Join, Aggregation, Grouping",62    "question": "Find the total number of items sold for each media type.",63    "gold_sql": "SELECT MediaType.Name, SUM(InvoiceLine.Quantity) FROM InvoiceLine JOIN Track ON InvoiceLine.TrackId = Track.TrackId JOIN MediaType ON Track.MediaTypeId = MediaType.MediaTypeId GROUP BY MediaType.Name;"64  },65  {66    "id": 10,67    "difficulty": "easy",68    "taxonomy": "Selection, Filtering",69    "question": "List the first and last names of all employees who are Sales Support Agents.",70    "gold_sql": "SELECT FirstName, LastName FROM Employee WHERE Title = 'Sales Support Agent';"71  },72  {73    "id": 11,74    "difficulty": "medium",75    "taxonomy": "Simple Join, Aggregation, Grouping, Ordering, Limit",76    "question": "List the top 5 customers who have spent the most money in total.",77    "gold_sql": "SELECT c.FirstName, c.LastName, SUM(i.Total) as TotalSpent FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId GROUP BY c.CustomerId ORDER BY TotalSpent DESC LIMIT 5;"78  },79  {80    "id": 12,81    "difficulty": "hard",82    "taxonomy": "Multi-Join, Aggregation, Grouping, Ordering, Limit",83    "question": "Which artist has the most tracks in the database? Give the name and count.",84    "gold_sql": "SELECT ar.Name, COUNT(t.TrackId) as TrackCount FROM Artist ar JOIN Album al ON ar.ArtistId = al.ArtistId JOIN Track t ON al.AlbumId = t.AlbumId GROUP BY ar.ArtistId ORDER BY TrackCount DESC LIMIT 1;"85  },86  {87    "id": 13,88    "difficulty": "medium",89    "taxonomy": "Simple Join, Aggregation, Grouping, Having",90    "question": "Which genres have more than 100 tracks? List the genre name and count.",91    "gold_sql": "SELECT g.Name, COUNT(t.TrackId) as TrackCount FROM Genre g JOIN Track t ON g.GenreId = t.GenreId GROUP BY g.GenreId HAVING TrackCount > 100;"92  },93  {94    "id": 14,95    "difficulty": "medium",96    "taxonomy": "Simple Join, Aggregation, Arithmetic, Grouping",97    "question": "Calculate the average track length in seconds for each genre.",98    "gold_sql": "SELECT g.Name, AVG(t.Milliseconds) / 1000.0 as AvgSeconds FROM Genre g JOIN Track t ON g.GenreId = t.GenreId GROUP BY g.GenreId;"99  },100  {101    "id": 15,102    "difficulty": "hard",103    "taxonomy": "Multi-Join, Aggregation, Grouping, Ordering, Limit",104    "question": "Identify the artist who has earned the most revenue from customers in Canada.",105    "gold_sql": "SELECT ar.Name, SUM(il.UnitPrice * il.Quantity) AS Revenue FROM Artist ar JOIN Album al ON ar.ArtistId = al.ArtistId JOIN Track t ON al.AlbumId = t.AlbumId JOIN InvoiceLine il ON t.TrackId = il.TrackId JOIN Invoice i ON il.InvoiceId = i.InvoiceId WHERE i.BillingCountry = 'Canada' GROUP BY ar.ArtistId ORDER BY Revenue DESC LIMIT 1;"106  }107]