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
What was the percentage of the Bitcoins verifiably burned until 2018/4/28?

### Hint
the percentage of the Bitcoins verifiably burned = divide(subtract(SUM(max_supply), SUM(total_supply)),SUM(total_supply))*100%; until 2013/4/28 refers to date<'2013-04-08'

### SQL
SELECT CAST((SUM(T2.max_supply) - SUM(T2.total_supply)) AS REAL) / SUM(T2.total_supply) FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date < '2018-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
Please list the names of coins that has been disappeared.

### Hint
has disappeared refers to status = 'extinct'

### SQL
SELECT name FROM coins WHERE status = 'extinct'
### 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's the descripition of BitBar?

### SQL
SELECT description FROM coins WHERE name = 'BitBar'
### 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 many coins were added in May 2013? Please list the names of coins.

### Hint
in May 2013 refers to year(date_added) = 2013 and month(date_added) = 5

### SQL
SELECT COUNT(id) num FROM coins WHERE STRFTIME('%Y-%m', date_added) = '2013-05' UNION ALL SELECT name FROM coins WHERE STRFTIME('%Y-%m', date_added) = '2013-05'
### 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 names and symbols of the coins that were added on June 14, 2013.

### Hint
added on June 14, 2013 refers to date_added like '2013-06-14%'

### SQL
SELECT name, symbol FROM coins WHERE date_added LIKE '2013-06-14%'
### 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 names of coins that cannot be traded in 2014.

### Hint
cannot be tracked refers to status = 'untracked'; in 2014 refers to year(date_added) = '2014'

### SQL
SELECT name FROM coins WHERE date_added LIKE '2014%' AND status = 'untracked'
### 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 coins that have three tags.

### Hint
have three tags refers to length(tag_names)-length(replace(tag_names,',','')) = 2

### SQL
SELECT name FROM coins WHERE LENGTH(tag_names) - LENGTH(replace(tag_names, ',', '')) = 2
### 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 name of the coin with the highest price?

### Hint
the highest price refers to max(price)

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.price = ( SELECT MAX(price) 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
Please name the coin that ranked first among the coins traded on April 29, 2013.

### Hint
ranked first refers to cmc_rank = 1; on April 29, 2013 refers to date = '2013-04-29'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-29' 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
When is the best time to purchase Bitcoin?

### Hint
lowest price refers to low; best time refers to date with the low;

### SQL
SELECT T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Bitcoin' ORDER BY T2.low 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 is the name of the coin that creates the most total value in the past 24 hours?

### Hint
creates the most total value in the past 24 hours refers to max(volume_24h)

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.volume_24h = ( SELECT MAX(volume_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
Name the coins that were not opened on May 2013.

### Hint
not opened refers to open IS NULL; on May 2013 refers to year(date) = 2019 AND month(date) = 5

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE STRFTIME('%Y-%m', T2.date) = '2013-05' 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
When is the highest closed price of CHNCoin?

### Hint
the highest closed price refers to max(close)

### SQL
SELECT T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'CHNCoin' ORDER BY T2.close 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
When did Peercoin rank fifth?

### Hint
when refers to date; rank fifth refers to cmc_rank = 5

### SQL
SELECT T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Peercoin' AND T2.cmc_rank = 5
### 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 is Devcoin most valuable in the market?

### Hint
when refers to date; most valuable refers to max(market_cap)

### SQL
SELECT T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Devcoin' ORDER BY T2.market_cap 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
List the names of the top five coins traded on January 1, 2014.

### Hint
the top five coins refers to cmc_rank< = 5; on January 1, 2014 refers to date = '2014-01-01'

### SQL
SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2014-01-01' AND T2.cmc_rank <= 5
### 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 was Lebowskis not opened?

### Hint
when refers to date; not opened refers to open IS NULL

### SQL
SELECT DISTINCT T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Lebowskis' AND (T2.open IS NULL OR T2.open = 0)
### 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 is the highest price of Terracoin?

### Hint
when refers to date; the highest price refers to max(price)

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