lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
What is the establishment's name with an inspection category of No Smoking Regulations?

### Hint
establishment's name refers to dba_name; an inspection category of No Smoking Regulations refers to category = 'No Smoking Regulations'

### SQL
SELECT DISTINCT T1.dba_name FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no INNER JOIN violation AS T3 ON T2.inspection_id = T3.inspection_id INNER JOIN inspection_point AS T4 ON T3.point_id = T4.point_id WHERE T4.category = 'No Smoking Regulations'
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
What is the difference in the number of restaurants that passed and failed the canvass inspection type?

### Hint
canvass inspection type refers to inspection_type = 'Canvass'; restaurant refers to facility_type = 'Restaurant'; difference = subtract(count(inspection_id where results = 'Pass'), count(inspection_id where results = 'Fail'))

### SQL
SELECT COUNT(CASE WHEN T2.results = 'Pass' THEN T1.license_no END) - COUNT(CASE WHEN T2.results = 'Fail' THEN T1.license_no END) AS diff FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE T2.inspection_type = 'Canvass' AND T1.facility_type = 'Restaurant'
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
Among the establishments that failed the inspection in February 2010, list the names of the employees with a salary greater than 70% of the average salary of all employees.

### Hint
failed the inspection refers to results = 'Fail'; in January 2010 refers to inspection_date like '2010-01%'; name of employee refers to first_name, last_name; a salary greater than 70% of the average salary refers to salary > multiply(avg(salary), 0.7)

### SQL
SELECT DISTINCT T1.employee_id FROM employee AS T1 INNER JOIN inspection AS T2 ON T1.employee_id = T2.employee_id WHERE T2.results = 'Fail' AND strftime('%Y-%m', T2.inspection_date) = '2010-02' AND T1.salary > 0.7 * ( SELECT AVG(salary) FROM employee )
### Database Schema
CREATE TABLE employee
(
    employee_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    address     TEXT,
    city        TEXT,
    state       TEXT,
    zip         INTEGER,
    phone       TEXT,
    title       TEXT,
    salary      INTEGER,
    supervisor  INTEGER,
    foreign key (supervisor) references employee(employee_id)
)

CREATE TABLE establishment
(
    license_no    INTEGER
            primary key,
    dba_name      TEXT,
    aka_name      TEXT,
    facility_type TEXT,
    risk_level    INTEGER,
    address       TEXT,
    city          TEXT,
    state         TEXT,
    zip           INTEGER,
    latitude      REAL,
    longitude     REAL,
    ward          INTEGER
)

CREATE TABLE inspection
(
    inspection_id   INTEGER
            primary key,
    inspection_date DATE,
    inspection_type TEXT,
    results         TEXT,
    employee_id     INTEGER,
    license_no      INTEGER,
    followup_to     INTEGER,
    foreign key (employee_id) references employee(employee_id),
    foreign key (license_no) references establishment(license_no),
    foreign key (followup_to) references inspection(inspection_id)
)

CREATE TABLE inspection_point
(
    point_id    INTEGER
            primary key,
    Description TEXT,
    category    TEXT,
    code        TEXT,
    fine        INTEGER,
    point_level TEXT
)

CREATE TABLE violation
(
    inspection_id     INTEGER,
    point_id          INTEGER,
    fine              INTEGER,
    inspector_comment TEXT,
    primary key (inspection_id, point_id),
    foreign key (inspection_id) references inspection(inspection_id),
    foreign key (point_id) references inspection_point(point_id)
)

### Question
Among the establishments that paid a 500 fine, what is the percentage of restaurants?

### Hint
a 500 fine refers to fine = 500; restaurant refers to facility_type = 'Restaurant'; percentage = divide(count(license_no where facility_type = 'Restaurant'), count(license_no)) * 100% where fine = 500

### SQL
SELECT CAST(COUNT(CASE WHEN T1.facility_type = 'Restaurant' THEN T1.license_no END) AS REAL) * 100 / COUNT(T1.facility_type) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no INNER JOIN violation AS T3 ON T2.inspection_id = T3.inspection_id WHERE T3.fine = 500
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
Name the coin that has the highest market capitalization for all transactions in 2018.

### Hint
highest market capitalization refers to max(market_cap); in 2018 refers to year(date) = 2018

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date LIKE '2018%' AND T2.market_cap = ( SELECT MAX(market_cap) FROM historical WHERE STRFTIME('%Y', date) = '2018' )
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
What is the total value of Argentum coined traded in the past 24 hours on 2016/10/11.

### Hint
total value in the past 24 hours refers to volume_24h; on 11/10/16 refers to date = '2016-11-10'

### SQL
SELECT T2.volume_24h FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Argentum' AND T2.date = '2016-10-11'
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
List the price for Zetacoin on 13/11/1 and the next 7 consecutive days. What is the average price for these 7 days?

### Hint
on 1/11/13 and the next 7 consecutive days refers to DATE BETWEEN '2013-11-01' AND '2013-11-07'; average price refers to AVG(price); Zetacoin refers to name = 'Zetacoin'

### SQL
SELECT T2.price FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Zetacoin' AND T2.date BETWEEN '2013-11-01' AND '2013-11-07' UNION ALL SELECT AVG(T2.PRICE) FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Zetacoin' AND T2.date BETWEEN '2013-11-01' AND '2013-11-07'
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
For all transactions for WRAP in August 2016, list the time to achieve highest price and the time to achieve the lowest price.

### Hint
in May 2013 refers to month(date) = 5 AND year(date) = 2013; time to achieve the highest price refers to time_high; time to achieve the lowest price refers to time_low; WRAP refers to name = 'WARP'

### SQL
SELECT T2.time_high, T2.time_low, T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'WARP' AND STRFTIME('%Y-%m', T2.date) = '2016-08'
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
State the transaction date whereby DigixDAO was transacted at the hightest price.

### Hint
the highest price refers to max(price); DigixDAO refers to name = 'DigixDAO'

### SQL
SELECT T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'DigixDAO' ORDER BY T2.price DESC LIMIT 1
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
Name the coin with the highest percentage price changed in 24 hours. State the transaction date and price.

### Hint
the highest percentage price changed in 24 hours refers to max(percent_change_24h)

### SQL
SELECT T1.name, T2.DATE, T2.price FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.percent_change_24h = ( SELECT MAX(percent_change_24h) FROM historical )
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
What is the average monthly circulating supply for Frozen in 2014.

### Hint
average monthly circulating supply = AVG(circulating_supply); in 2017 refers to date BETWEEN '2017-01-01' AND '2017-12-31'

### SQL
SELECT CAST(SUM(T2.circulating_supply) AS REAL) / 12 FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Frozen' AND STRFTIME('%Y', T2.date) = '2014'
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
List all the inactive coins and state the last date of its transaction?

### Hint
the last date refers to max(date); inactive coins refers to status = 'inactive'

### SQL
SELECT T1.NAME, MAX(T2.DATE) FROM coins AS T1 INNER JOIN historical AS T2 ON T1.ID = T2.coin_id WHERE T1.status = 'inactive' ORDER BY T2.DATE DESC LIMIT 1
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
What was the price of 1 Bitcoin in 2016?

### Hint
2016 refers to historical date where year(date) = 2016

### SQL
SELECT AVG(T2.price) FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Bitcoin' AND STRFTIME('%Y', T2.date) = '2016'
### Database Schema
CREATE TABLE coins
(
    id            INTEGER not null
        primary key,
    name          TEXT,
    slug          TEXT,
    symbol        TEXT,
    status        TEXT,
    category      TEXT,
    description   TEXT,
    subreddit     TEXT,
    notice        TEXT,
    tags          TEXT,
    tag_names     TEXT,
    website       TEXT,
    platform_id   INTEGER,
    date_added    TEXT,
    date_launched TEXT
)

CREATE TABLE "historical"
(
    date               DATE,
    coin_id            INTEGER,
    cmc_rank           INTEGER,
    market_cap         REAL,
    price              REAL,
    open               REAL,
    high               REAL,
    low                REAL,
    close              REAL,
    time_high          TEXT,
    time_low           TEXT,
    volume_24h         REAL,
    percent_change_1h  REAL,
    percent_change_24h REAL,
    percent_change_7d  REAL,
    circulating_supply REAL,
    total_supply       REAL,
    max_supply         REAL,
    num_market_pairs   INTEGER
)

### Question
State the transaction date and the price when Bitcoin was bottomed?

### Hint
was bottomed refers to min(price)

### SQL
SELECT T2.date, T2.price FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Bitcoin' ORDER BY T2.price LIMIT 1