lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
What is the content of the earliest review for the "Stuff You Should Know" podcast?

### Hint
"Stuff You Should Know" is the title of podcast; earliest refers to Min(created_at)

### SQL
SELECT T2.content FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'Stuff You Should Know' ORDER BY T2.created_at ASC LIMIT 1
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
How many reviews does "Planet Money" have?

### Hint
"Planet Money" is the title of podcast

### SQL
SELECT COUNT(T2.podcast_id) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'Planet Money'
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
What is the average rating for the "crime-junkie" podcast?

### Hint
"crime-junkie" podcast refers to title = 'crime-junkie'; average rating = Divide (Sum(rating), Count(rating))

### SQL
SELECT AVG(T2.rating) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'Crime Junkie'
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
What percentage of podcasts are "technology" podcasts? List all of them.

### Hint
"technology" podcast refers to category = 'technology'; percentage = Divide (Count (podcast_id (category = 'technology')), Count (podcast_id)) * 100

### SQL
SELECT CAST(SUM(CASE WHEN T1.category = 'technology' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T2.title) OR '%' "percentage" FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
What is the content of the review under the title "really interesting!" and is created on 2018-04-24 at 12:05:16?

### Hint
"really interesting" is the title of review;  created on 2018-04-24 at 12:05:16 refers to created_at = '2018-04-24T12:05:16-07:00'

### SQL
SELECT content FROM reviews WHERE title = 'really interesting!' AND created_at = '2018-04-24T12:05:16-07:00'
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
Which category is the podcast "Scaling Global" under?

### Hint
"Scaling Global" is the title of podcast

### SQL
SELECT category FROM categories WHERE podcast_id IN ( SELECT podcast_id FROM podcasts WHERE title = 'Scaling Global' )
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
Please list the titles of all the podcasts under the category "arts-performing-arts".

### Hint
category 'arts-performing-arts' refers to category = 'arts-performing-arts';

### SQL
SELECT DISTINCT T2.title FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.category = 'arts-performing-arts'
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
How many reviews are created for the podcast "Scaling Global" under?

### Hint
"Scaling Global" is the title of podcast

### SQL
SELECT COUNT(T2.content) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'Scaling Global'
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
Among the reviews for the podcast "Please Excuse My Dead Aunt Sally", how many of them are made in the year 2019?

### Hint
"Please Excuse My Dead Aunt Sally" is the title of podcast; made in the year 2019 refers to created_at like '2019%'

### SQL
SELECT COUNT(T2.created_at) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'Please Excuse My Dead Aunt Sally' AND T2.created_at LIKE '2019-%'
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
Please list the titles of the podcasts for which the author whose ID is F7E5A318989779D has written a review.

### Hint
author whose ID is F7E5A318989779D refers to author_id = 'F7E5A318989779D'

### SQL
SELECT T2.title FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.author_id = 'F7E5A318989779D'
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
How many ratings of 5 have been given to the podcast "Please Excuse My Dead Aunt Sally"?

### Hint
rating of 5 refers to rating = 5; 'Please Excuse My Dead Aunt Sally' is the title of podcast

### SQL
SELECT COUNT(T2.rating) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'Please Excuse My Dead Aunt Sally' AND T2.rating = 5
### Database Schema
CREATE TABLE runs (
        run_at text not null,
        max_rowid integer not null,
        reviews_added integer not null
    )

CREATE TABLE podcasts (
        podcast_id text primary key,
        itunes_id integer not null,
        slug text not null,
        itunes_url text not null,
        title text not null
    )

CREATE TABLE "reviews"
(
    podcast_id TEXT    not null
        constraint reviews_podcasts_podcast_id_fk
            references podcasts,
    title      TEXT    not null,
    content    TEXT    not null,
    rating     INTEGER not null,
    author_id  TEXT    not null,
    created_at TEXT    not null
)

CREATE TABLE "categories"
(
    podcast_id TEXT not null
        constraint categories_podcasts_podcast_id_fk
            references podcasts,
    category   TEXT not null,
    constraint "PRIMARY"
        primary key (podcast_id, category)
)

### Question
What is the average rating of the podcast "Please Excuse My Dead Aunt Sally"?

### Hint
"Please Excuse My Dead Aunty Sally" is the title of podcast; Average rating = Divide (Sum(rating), Count(rating))

### SQL
SELECT AVG(T2.rating) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'Please Excuse My Dead Aunt Sally'
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
How many universities have at least 80,000 students in the year 2011?

### Hint
have at least 80,000 students refers to num_students > 8000; year = 2011

### SQL
SELECT COUNT(*) FROM university_year WHERE num_students > 80000 AND year = 2011
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
What is the ranking system ID of the award criteria?

### Hint
award criteria refers to criteria_name = 'Award';

### SQL
SELECT ranking_system_id FROM ranking_criteria WHERE criteria_name = 'Award'
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
How many state universities are there?

### Hint
state universities refers to university_name LIKE '%State%';

### SQL
SELECT COUNT(*) FROM university WHERE university_name LIKE '%State%'
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
What is the student staff ratio of the university with the highest student staff ratio of all time?

### Hint
highest student staff ratio refers to max(student_staff_ratio)

### SQL
SELECT MAX(student_staff_ratio) FROM university_year WHERE student_staff_ratio = ( SELECT MAX(student_staff_ratio) FROM university_year )
### Database Schema
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
)

CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
)

CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
)

CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
)

CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
)

CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
)

### Question
How many criteria belong to ranking system ID 3?

### SQL
SELECT COUNT(id) FROM ranking_criteria WHERE ranking_system_id = 3