lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
List the top 5 lowest rated puzzle games and count the number of negative sentiments the games received.

### Hint
lowest rating refers to MIN(Rating); puzzle is the genre;

### SQL
SELECT T1.App, COUNT(T1.App) COUNTNUMBER FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T2.Sentiment = 'Negative' GROUP BY T1.App ORDER BY T1.Rating LIMIT 5
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
What is the percentage ratio between positive sentiments and negative sentiments that are in Fate/Grand Order? Also indicate the current version.

### Hint
Fate/Grand Order is the App; percentage ratio = MULTIPLY(DIVIDE((SUM(Sentiment = 'Positive')), (SUM(Sentiment = 'Negative'))), 100);

### SQL
SELECT CAST(SUM(CASE WHEN T2.Sentiment = 'Positive' THEN 1 ELSE 0 END) AS REAL) * 100 / SUM(CASE WHEN T2.Sentiment = 'Negative' THEN 1 ELSE 0 END), T1.`Current Ver` FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Fate/Grand Order (English)' AND T1.`Current Ver` = '1.18.0'
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
Indicate the number of installs and include the percentage of positive sentiments of FREEDOME VPN Unlimited anonymous Wifi Security.

### Hint
FREEDOME VPN Unlimited anonymous Wifi Security is the App; percentage = MULTIPLY(DIVIDE((SUM(Sentiment = 'Positive')), (COUNT(*))), 100)

### SQL
SELECT T1.Installs , CAST(SUM(CASE WHEN T2.Sentiment = 'Positive' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN T2.Sentiment IS NOT NULL THEN 1.0 ELSE 0 END) AS REAL) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'FREEDOME VPN Unlimited anonymous Wifi Security'
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
For the Honkai Impact 3rd App, what is the highest sentiment polarity score and what genre does it belong to?

### Hint
highest sentiment polarity score refers to MAX(Sentiment_Polarity);

### SQL
SELECT MAX(T2.Sentiment_Polarity), T1.Genres FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Honkai Impact 3rd' AND T2.Sentiment_Polarity > 0.5 GROUP BY T1.Genres
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
What is the rating of Dragon Ball Legends and how many users dislike this App?

### Hint
Dragon Ball Legends is the app; users who dislikes the app refers to Sentiment_Polarity<-0.5;

### SQL
SELECT T1.Rating, COUNT(T2.Sentiment_Polarity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Dragon Ball Legends' AND CAST(Sentiment_Polarity AS INTEGER) < -0.5
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
Which education App has the worst rating and state the translated review if available.

### Hint
education App refers to Category = 'EDUCATION'; worst rated app refers to Rating = 1;

### SQL
SELECT T1.App, T2.Translated_Review FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Category = 'EDUCATION' GROUP BY T1.App, T2.Translated_Review ORDER BY T1.Rating ASC LIMIT 1
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
List all free sports Apps and their translated review.

### Hint
paid sports Apps refers to type = 'Paid' and Category = 'SPORTS';

### SQL
SELECT T1.App, T2.Translated_Review FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Type = 'Free' AND T1.Category = 'SPORTS'
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
Among the role playing game genre, how many are targeted to teens and what is their average sentiment polarity score?

### Hint
targeted to teen refers to Content Rating = 'Teen'; average = AVG(Sentiment_Polarity);

### SQL
SELECT COUNT(T1.App), AVG(T2.Sentiment_Polarity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1."Content Rating" = 'Teen' AND T1.Genres = 'Role Playing'
### Database Schema
CREATE TABLE "playstore"
(
    App              TEXT,
    Category         TEXT,
    Rating           REAL,
    Reviews          INTEGER,
    Size             TEXT,
    Installs         TEXT,
    Type             TEXT,
    Price            TEXT,
    "Content Rating" TEXT,
    Genres           TEXT
)

CREATE TABLE "user_reviews"
(
    App                    TEXT
        references "playstore" (App),
    Translated_Review      TEXT,
    Sentiment              TEXT,
    Sentiment_Polarity     TEXT,
    Sentiment_Subjectivity TEXT
)

### Question
What is the average rating of Apps falling under the racing genre and what is the percentage ratio of positive sentiment reviews?

### Hint
average rating = AVG(Rating); percentage = MULTIPLY(DIVIDE((SUM(Sentiment = 'Positive')), (COUNT(*)), 100));

### SQL
SELECT AVG(T1.Rating), CAST(COUNT(CASE WHEN T2.Sentiment = 'Positive' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T2.Sentiment) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Genres = 'Racing'
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Which region has the most number of sales team?

### Hint
the most number of sales team refers to MAX(COUNT(Sales Team));

### SQL
SELECT Region FROM `Sales Team` GROUP BY Region ORDER BY COUNT(DISTINCT `Sales Team`) DESC LIMIT 1
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
List all the customers with name containing the word 'Group'.

### Hint
name containing the word 'Group' refers to Customer Names LIKE '%Group%';

### SQL
SELECT T FROM ( SELECT IIF(`Customer Names` LIKE '%Group%', `Customer Names`, NULL) AS T FROM Customers ) WHERE T IS NOT NULL
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
What is the average median income for all City type of stores?

### Hint
AVG(Median Income) where Type = 'City';

### SQL
SELECT AVG(`Median Income`) FROM `Store Locations` WHERE Type = 'City'
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
Name the sales team and the region of order number 'SO - 000137'.

### SQL
SELECT T2.`Sales Team`, T2.Region FROM `Sales Orders` AS T1 INNER JOIN `Sales Team` AS T2 ON T2.SalesTeamID = T1._SalesTeamID WHERE T1.OrderNumber = 'SO - 000137'
### Database Schema
CREATE TABLE Customers
(
    CustomerID       INTEGER
        constraint Customers_pk
            primary key,
    "Customer Names" TEXT
)

CREATE TABLE Products
(
    ProductID      INTEGER
        constraint Products_pk
            primary key,
    "Product Name" TEXT
)

CREATE TABLE Regions
(
    StateCode TEXT
        constraint Regions_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE "Sales Team"
(
    SalesTeamID  INTEGER
        constraint "Sales Team_pk"
            primary key,
    "Sales Team" TEXT,
    Region       TEXT
)

CREATE TABLE "Store Locations"
(
    StoreID            INTEGER
        constraint "Store Locations_pk"
            primary key,
    "City Name"        TEXT,
    County             TEXT,
    StateCode          TEXT
        constraint "Store Locations_Regions_StateCode_fk"
            references Regions(StateCode),
    State              TEXT,
    Type               TEXT,
    Latitude           REAL,
    Longitude          REAL,
    AreaCode           INTEGER,
    Population         INTEGER,
    "Household Income" INTEGER,
    "Median Income"    INTEGER,
    "Land Area"        INTEGER,
    "Water Area"       INTEGER,
    "Time Zone"        TEXT
)

CREATE TABLE "Sales Orders"
(
    OrderNumber        TEXT
        constraint "Sales Orders_pk"
            primary key,
    "Sales Channel"    TEXT,
    WarehouseCode      TEXT,
    ProcuredDate       TEXT,
    OrderDate          TEXT,
    ShipDate           TEXT,
    DeliveryDate       TEXT,
    CurrencyCode       TEXT,
    _SalesTeamID       INTEGER
        constraint "Sales Orders_Sales Team_SalesTeamID_fk"
            references "Sales Team"(SalesTeamID),
    _CustomerID        INTEGER
        constraint "Sales Orders_Customers_CustomerID_fk"
            references Customers(CustomerID),
    _StoreID           INTEGER
        constraint "Sales Orders_Store Locations_StoreID_fk"
            references "Store Locations"(StoreID),
    _ProductID         INTEGER
        constraint "Sales Orders_Products_ProductID_fk"
            references Products(ProductID),
    "Order Quantity"   INTEGER,
    "Discount Applied" REAL,
    "Unit Price"       TEXT,
    "Unit Cost"        TEXT
)

### Question
List all the order numbers along with its product name for each order under the sales team of 'Douglas Tucker'.

### SQL
SELECT DISTINCT T1.ProductID, T1.`Product Name` FROM Products AS T1 INNER JOIN `Sales Orders` AS T2 ON T2._ProductID = T1.ProductID INNER JOIN `Sales Team` AS T3 ON T3.SalesTeamID = T2._SalesTeamID WHERE T3.`Sales Team` = 'Douglas Tucker'