lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
How many reviews by people between 30 and 50 years include the word 'great'?

### Hint
between 30 and 50 years refers to age BETWEEN 30 AND 50; include the word great refers to Review like '%Great%';

### SQL
SELECT COUNT(T1.Reviews) FROM reviews AS T1 INNER JOIN client AS T2 ON T1.district_id = T2.district_id WHERE T2.age BETWEEN 30 AND 50 AND T1.Reviews LIKE '%great%'
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
What is the full address of the customers who, having received a timely response from the company, have dispute about that response?

### Hint
full address = address_1, address_2; received a timely response refers to Timely response? = 'Yes'; have dispute refers to "Consumer disputed?" = 'Yes';

### SQL
SELECT T1.address_1, T1.address_2 FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T2.`Timely response?` = 'Yes' AND T2.`Consumer disputed?` = 'Yes'
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
How many complaints from female clients born in the year 2000 were not sent through the web?

### Hint
female refers to sex = 'Female'; sent through refers to "Submitted via"; not sent through web refers to "Submitted via" ! = 'Web';


### SQL
SELECT COUNT(T2.`Submitted via`) FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T1.sex = 'Female' AND T1.year = 2000 AND T2.`Submitted via` != 'Web'
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
List all the complaints narratives made by the customer named Brenda and last name Mayer.

### Hint
complaints narratives refers to "Consumer complaint narrative";

### SQL
SELECT T2.`Consumer complaint narrative` FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T1.first = 'Brenda' AND T1.last = 'Mayer'
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
How many complaints from customers with a gmail.com email were received by the company in February 2017?

### Hint
gmail.com email refers to email like '%gmail.com'; in February 2017 refers to "Date received" BETWEEN '2017-01-02' AND '2017-02-28';

### SQL
SELECT COUNT(T1.email) FROM client AS T1 INNER JOIN callcenterlogs AS T2 ON T1.client_id = T2.`rand client` WHERE (T2.`Date received` LIKE '2017-02%' OR T2.`Date received` LIKE '2017-01%') AND T1.email LIKE '%@gmail.com'
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
What is the average number of stars given by Oregon clients in their reviews?

### Hint
average = DIVIDE(SUM(State = 'Oregon'), COUNT(district_id)); Oregon refers to state = 'Oregon';

### SQL
SELECT CAST(SUM(T3.Stars) AS REAL) / COUNT(T3.Stars) AS average FROM state AS T1 INNER JOIN district AS T2 ON T1.StateCode = T2.state_abbrev INNER JOIN reviews AS T3 ON T2.district_id = T3.district_id WHERE T1.State = 'Oregon'
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
What percentage of clients who sent their complaints by postal mail are age 50 and older?

### Hint
percentage = MULTIPLY(DIVIDE(SUM("Submitted via" = 'Postal mail'), COUNT(client_id)), 1.0); sent their complaints by refers to "Submitted via"; age > 50;

### SQL
SELECT CAST(SUM(CASE WHEN T1.age > 50 THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T2.`Submitted via`) FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T2.`Submitted via` = 'Postal mail'
### Database Schema
CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
)

CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
)

CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
)

CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
)

CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
)

### Question
What is the average age of Norwalk clients?

### Hint
average age = AVG(age); Norwalk refers to city = 'Norwalk';

### SQL
SELECT CAST(SUM(T1.age) AS REAL) / COUNT(T1.age) AS average FROM client AS T1 INNER JOIN district AS T2 ON T1.district_id = T2.district_id WHERE T2.city = 'Norwalk'