dvwn/nl2sql-api

1[2    {3        "id": 1,4        "question": "List all the artists name in the database.",5        "taxonomy": "Selection",6        "ex_pass": true,7        "esm_pass": false,8        "generated_sql": "SELECT a.\"Name\" FROM \"Artist\" a;",9        "gold_sql": "SELECT Name FROM Artist;"10    },11    {12        "id": 2,13        "question": "How many genres are there?",14        "taxonomy": "Aggregation",15        "ex_pass": true,16        "esm_pass": false,17        "generated_sql": "SELECT COUNT(g.genreid) AS number_of_genres FROM genre g;",18        "gold_sql": "SELECT COUNT(*) FROM Genre;"19    },20    {21        "id": 3,22        "question": "List the names of the first 5 tracks.",23        "taxonomy": "Selection, Limit",24        "ex_pass": true,25        "esm_pass": false,26        "generated_sql": "SELECT t.name FROM \"Track\" t LIMIT 5;",27        "gold_sql": "SELECT Name FROM Track LIMIT 5;"28    },29    {30        "id": 4,31        "question": "Count the number of customers located in the USA.",32        "taxonomy": "Aggregation, Filtering",33        "ex_pass": false,34        "esm_pass": false,35        "generated_sql": "SELECT COUNT(*) FROM \"Customer\" c WHERE c.Country ILIKE '%USA%';",36        "gold_sql": "SELECT COUNT(*) FROM Customer WHERE Country = 'USA';"37    },38    {39        "id": 5,40        "question": "Find all invoices for the customer with ID 1.",41        "taxonomy": "Selection, Filtering",42        "ex_pass": true,43        "esm_pass": false,44        "generated_sql": "SELECT * FROM Invoice i WHERE i.CustomerId = 1;",45        "gold_sql": "SELECT * FROM Invoice WHERE CustomerId = 1;"46    },47    {48        "id": 6,49        "question": "List each album title along with the artist's name.",50        "taxonomy": "Simple Join",51        "ex_pass": true,52        "esm_pass": false,53        "generated_sql": "SELECT a.\"Title\", ar.\"Name\" AS \"Artist Name\" FROM \"Album\" a JOIN \"Artist\" ar ON a.\"ArtistId\" = ar.\"ArtistId\";",54        "gold_sql": "SELECT Album.Title, Artist.Name FROM Album JOIN Artist ON Album.ArtistId = Artist.ArtistId;"55    },56    {57        "id": 7,58        "question": "How many tracks belong to the 'Rock' genre?",59        "taxonomy": "Simple Join, Filtering, Aggregation",60        "ex_pass": true,61        "esm_pass": false,62        "generated_sql": "SELECT COUNT(t.TrackId) AS number_of_tracks FROM \"Track\" t JOIN \"Genre\" g ON t.GenreId = g.GenreId WHERE g.Name = 'Rock';",63        "gold_sql": "SELECT COUNT(*) FROM Track JOIN Genre ON Track.GenreId = Genre.GenreId WHERE Genre.Name = 'Rock';"64    },65    {66        "id": 8,67        "question": "Show the total revenue generated from each country.",68        "taxonomy": "Aggregation, Grouping",69        "ex_pass": false,70        "esm_pass": false,71        "generated_sql": "SELECT c.country_name, SUM(i.total) AS total_revenue FROM \"Invoice\" i JOIN \"Customer\" c ON i.customer_id = c.customer_id GROUP BY c.country_name ORDER BY total_revenue DESC NULLS LAST;",72        "gold_sql": "SELECT BillingCountry, SUM(Total) FROM Invoice GROUP BY BillingCountry;"73    },74    {75        "id": 9,76        "question": "Find the total number of items sold for each media type.",77        "taxonomy": "Multi-Join, Aggregation, Grouping",78        "ex_pass": false,79        "esm_pass": false,80        "generated_sql": "SELECT m.name AS media_type, COUNT(*) AS total_items_sold FROM \"MediaType\" m JOIN \"Sales\" s ON m.media_type_id = s.media_type_id GROUP BY m.name;",81        "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;"82    },83    {84        "id": 10,85        "question": "List the first and last names of all employees who are Sales Support Agents.",86        "taxonomy": "Selection, Filtering",87        "ex_pass": false,88        "esm_pass": false,89        "generated_sql": "SELECT e.first_name, e.last_name FROM \"Employee\" e WHERE e.title = 'Sales Support Agent';",90        "gold_sql": "SELECT FirstName, LastName FROM Employee WHERE Title = 'Sales Support Agent';"91    },92    {93        "id": 11,94        "question": "List the top 5 customers who have spent the most money in total.",95        "taxonomy": "Simple Join, Aggregation, Grouping, Ordering, Limit",96        "ex_pass": false,97        "esm_pass": false,98        "generated_sql": "SELECT c.customerid, c.firstname, c.lastname, SUM(oi.productid * oi.quantity) AS total_spent FROM customer c JOIN orders o ON c.customerid = o.customerid JOIN orderitems oi ON o.orderid = oi.orderid GROUP BY c.customerid, c.firstname, c.lastname ORDER BY total_spent DESC LIMIT 5;",99        "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;"100    },101    {102        "id": 12,103        "question": "Which artist has the most tracks in the database? Give the name and count.",104        "taxonomy": "Multi-Join, Aggregation, Grouping, Ordering, Limit",105        "ex_pass": false,106        "esm_pass": false,107        "generated_sql": "",108        "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;"109    },110    {111        "id": 13,112        "question": "Which genres have more than 100 tracks? List the genre name and count.",113        "taxonomy": "Simple Join, Aggregation, Grouping, Having",114        "ex_pass": true,115        "esm_pass": false,116        "generated_sql": "SELECT g.name, COUNT(t.trackid) AS COUNT FROM genre g JOIN track t ON g.genreid = t.genreid GROUP BY g.name HAVING COUNT(t.trackid) > 100 ORDER BY COUNT DESC NULLS LAST;",117        "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;"118    },119    {120        "id": 14,121        "question": "Calculate the average track length in seconds for each genre.",122        "taxonomy": "Simple Join, Aggregation, Arithmetic, Grouping",123        "ex_pass": false,124        "esm_pass": false,125        "generated_sql": "SELECT g.Name, AVG(t.Milliseconds / 1000)) AS AverageDuration FROM Track t JOIN Genre g ON t.GenreId = g.GenreId GROUP BY g.Name ORDER BY AverageDuration DESC NULLS LAST;",126        "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;"127    },128    {129        "id": 15,130        "question": "Identify the artist who has earned the most revenue from customers in Canada.",131        "taxonomy": "Multi-Join, Aggregation, Grouping, Ordering, Limit",132        "ex_pass": false,133        "esm_pass": false,134        "generated_sql": "SELECT a.\"Name\" FROM \"Artist\" a JOIN \"Album\" al ON a.\"ArtistId\" = al.\"AlbumArtistId\" WHERE al.\"AlbumCountry\" = 'Canada' ORDER BY a.\"Name\" DESC NULLS LAST LIMIT 1;",135        "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;"136    }137]