lianghsun/bird-text2sql-bench huggingface dataset:
### 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 coins with average price more than $1000. State the current status of the coin.

### Hint
average price more than $1000 refers to AVG(price)>1000

### SQL
SELECT T1.status FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id GROUP BY T1.name HAVING AVG(T2.price) > 1000
### 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 and date of transactions with the greatest decline in percent change in 1 hour.

### Hint
the greatest decline in percent change in 1 hour refers to max(percent_change_1h)

### SQL
SELECT T1.name, T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.percent_change_1h = ( SELECT MIN(percent_change_1h) 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
Name the coin under the token category that gives the highest max profit.

### Hint
the highest max profit refers to max(subtract(high, low))

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.category = 'token' ORDER BY T2.high - T2.low 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 that have higher than average percentage price changed from the previous 24 hours for transaction on 2013/6/22.

### Hint
average percentage price changed from the previous 24 hours refers to AVG(percent_change_24h); on 15/5/2013 refers to DATE = '2013-04-15'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2020-06-22' GROUP BY T1.name HAVING AVG(T2.percent_change_24h) > T2.PRICE
### 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
Which crytocurrency was ranked the first by CoinMarketCap on 2013/4/28?

### Hint
ranked the first refers to cmc_rank = 1; on 2013/4/28 refers to date = '2013-04-28'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T2.cmc_rank = 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
How much dollars was a Bitcoin worth on 2013/4/28 according to the coin market?

### Hint
worth refers to price; on 2013/4/28 refers to date = '2013-04-28'

### SQL
SELECT T2.market_cap FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T1.name = 'Bitcoin'
### 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
Which crytocurrency was not opened on 2013/5/3?

### Hint
not opened refers to open IS NULL; on 2013/5/3 refers to date = '2013-05-03'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-05-03' AND T2.open IS NULL
### 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 Bitcoin when it closed at the end of the day on 2013/4/29?

### Hint
price when it closed refers to close; on 2013/4/29 refers to date = '2013-04-29'

### SQL
SELECT T2.close FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-29' AND T1.name = 'Bitcoin'
### 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
When did Bitcoin reach its highest price on 2013/4/29?

### Hint
time that a coin reaches its highest price refers to time_high; on 2013/4/29 refers to date = '2013-04-29'

### SQL
SELECT T2.time_high FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-29' AND T1.name = 'Bitcoin'
### 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 max profit a user can make on Bitcoin on 2013/4/28?

### Hint
the max profit = subtract(high, low); on 2013/4/28 refers to date = '2013-04-28'

### SQL
SELECT T2.high - T2.low FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T1.name = 'Bitcoin'
### 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 number of Bitcoins verifiably burned until 2013/4/28?

### Hint
the number of Bitcoins verifiably burned = max_supply - total_supply; 2013/4/28 refers to date = '2013-04-28'

### SQL
SELECT T2.max_supply - T2.total_supply FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T1.name = 'Bitcoin'
### 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
Which crytocurrency was traded in the highest value on 2016/1/8?

### Hint
traded in the highest value refers to max(volume_24h); on 2016/1/8 refers to date = '2016-01-08'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2016-01-08' AND T2.volume_24h = ( SELECT MAX(volume_24h) FROM historical WHERE date = '2016-01-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
Please list the names of the crytocurrencies that have a total amount of existence of over 10000000 on 2013/4/28.

### Hint
a total amount of existence of over 10000000 refers to total_supply>10000000; on 2013/4/28 refers to date = '2013-04-28'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T2.total_supply > 10000000
### 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
Had Bitcoin's price increased or decreased on 2013/5/5 compared with the price 7 days before?

### Hint
price increased refers to percent_change_7d>0; decreased refers percent_change_7d<0; on 2013/5/5 refers to date = '2013-05-05'

### SQL
SELECT (CASE WHEN T2.percent_change_7d > 0 THEN 'INCREASED' ELSE 'DECREASED' END) FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-05-05' AND T1.name = 'Bitcoin'
### 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
Which crytocurrency had a bigger number of coins circulating in the market and in the general public's hands on 2013/4/28, Bitcoin or Litecoin?

### Hint
a bigger number of coins circulating in the market refers to max(circulating_supply); on 2013/4/28 refers to date = '2013-04-28'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T1.name IN ('Bitcoin', 'Litecoin') ORDER BY T2.circulating_supply 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
How much was a Bitcoin on 2013/4/28?

### Hint
how much refers to price; on 2013/4/28 refers to date = '2013-04-28'

### SQL
SELECT T2.price FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T1.name = 'Bitcoin'
### 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 average price of a Bitcoin in the year 2013?

### Hint
average price refers SUM(price)/COUNT(named = 'Bitcoin'); in the year 2013 refers to year(date) = 2013

### SQL
SELECT AVG(T2.price) FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE STRFTIME('%Y', T2.date) = '2013' AND T1.name = 'Bitcoin'