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
List all the names of podcasts under the 'true crime' category.

### Hint
name of the podcast refers to title of the podcast

### SQL
SELECT T2.title FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.category = 'true-crime'
### 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
Write all the review content belonging to StormCast: The Official Warhammer Age of Sigmar Podcast.

### Hint
review content refers to content; 'StormCast: The Official Warhammer Age of Sigmar Podcast' is the title of podcast;

### SQL
SELECT content FROM reviews WHERE podcast_id = ( SELECT podcast_id FROM podcasts WHERE title = 'StormCast: The Official Warhammer Age of Sigmar Podcast' )
### 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
Write all the review titles and the contents belonging to the podcast 'More Stupider: A 90-Day Fiance Podcast' with a review rating of 1.

### Hint
podcast 'More Stupider: A 90-Day Fiance Podcast'  refers to title = 'More Stupider: A 90-Day Fiance Podcast'; rating of 1 refers to rating = 1

### SQL
SELECT title, content FROM reviews WHERE podcast_id = ( SELECT podcast_id FROM podcasts WHERE title = 'More Stupider: A 90-Day Fiance Podcast' ) AND rating = 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 'LifeAfter/The Message' have which were rated below 3?

### Hint
LifeAfter/The Message' is the title of podcast; rated below 3 refers to rating < 3

### SQL
SELECT COUNT(T2.rating) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'LifeAfter/The Message' AND T2.rating <= 3
### 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
The 'More Stupider: A 90-Day Fiance Podcast' belongs to which category and what is the average rating of the podcast?

### Hint
More Stupider: A 90-Day Fiance Podcast' is the title of podcast; average rating = Divide (Sum(rating), Count(rating))

### SQL
SELECT AVG(T3.rating) FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id INNER JOIN reviews AS T3 ON T3.podcast_id = T2.podcast_id WHERE T2.title = 'More Stupider: A 90-Day Fiance Podcast'
### 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
Of the arts-books and arts-design categories, which one has more podcasts and what is the numerical difference between them?

### Hint
arts-books' and 'arts-design' are category; numerical difference = Subtract(Count(podcast_id(category = 'arts-books')), Count(podcast_id(category = 'arts-design'))); one has much more podcast refers to Max(Count(podcast_id))

### SQL
SELECT ( SELECT category FROM categories WHERE category = 'arts-books' OR category = 'arts-design' GROUP BY category ORDER BY COUNT(podcast_id) DESC LIMIT 1 ) "has more podcasts" , ( SELECT SUM(CASE WHEN category = 'arts-books' THEN 1 ELSE 0 END) - SUM(CASE WHEN category = 'arts-design' THEN 1 ELSE 0 END) FROM categories ) "differenct BETWEEN arts-books and arts-design"
### 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 total reviews runned at in June 2022 were added to the podcasts?

### Hint
run at in June 2022 refers to run_at BETWEEN '2022-06-01 00:00:00' and '2022-06-30 23:59:59'; reviews refers to review_added

### SQL
SELECT SUM(reviews_added) FROM runs WHERE run_at LIKE '2022-06-%'
### 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 podcast reviews with a rating of 3 were created during the first quarter of 2015?

### Hint
rating of 3 refers to rating = 3; created during the first quarter of 2015 refers to created_at BETWEEN'2015-01-01T00:00:00-07:00' and '2015-03-31T23:59:59-07:00'

### SQL
SELECT COUNT(podcast_id) FROM reviews WHERE rating = 3 AND created_at BETWEEN '2015-01-01T00:00:00-07:00' AND '2015-03-31T23:59:59-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
Calculate the percentage of podcasts in the fiction-science-fiction category.

### Hint
percentage =   Divide (Count(podcast_id(category = 'fiction-science-fiction')), Count(podcast_id)) * 100

### SQL
SELECT CAST(SUM(CASE WHEN category = 'fiction-science-fiction' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(podcast_id) OR '%' "percentage" FROM categories
### 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 all the podcasts with reviews created in 2019?

### Hint
created in 2019 refers to created_at BETWEEN '2019-01-01T00:00:00' and '2019-12-31T23:59:59'; average rating  = Divide (Sum(rating), Count(podcast_id))

### SQL
SELECT AVG(rating) FROM reviews WHERE created_at BETWEEN '2019-01-01T00:00:00-07:00' AND '2019-12-31T23:59:59-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
What is the percentage of reviews added each year of the total reviews added?

### Hint
review added each year refers to runs_at like '2021%' and runs_at like '2022%'; percentage for 2021 = Divide (Sum(reviews_added(runs_at like '2021%)), Sum(reviews_added)) * 100; percentage of 2022 = Divide (Sum(reviews_added(runs_at like '2022%')), Sum(reviews_added)) * 100

### SQL
SELECT CAST((SUM(CASE WHEN run_at LIKE '2022-%' THEN reviews_added ELSE 0 END) - SUM(CASE WHEN run_at LIKE '2021-%' THEN reviews_added ELSE 0 END)) AS REAL) * 100 / SUM(reviews_added) OR '%' "percentage" FROM runs
### 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
Indicates the title of all podcasts in the fiction category.

### SQL
SELECT T2.title FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.category = 'fiction'
### 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 rating and category of the podcast entitled Sitcomadon?

### Hint
entitled refers to title; 'Sitcomadon' is the title of podcast

### SQL
SELECT DISTINCT T3.rating, T1.category FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id INNER JOIN reviews AS T3 ON T3.podcast_id = T2.podcast_id WHERE T2.title = 'Sitcomadon'
### 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
Indicate the id of the reviewer whose itunes id is 1516665400.

### Hint
"151665400" is itunes_id; id of reviewer refers to author_id

### SQL
SELECT T2.author_id FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.itunes_id = 1516665400
### 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 are the titles of the podcasts whose reviews were created between 2018-08-22T11:53:16-07:00 and 2018-11-20T11:14:20-07:00?

### Hint
created between 2018-08-22T11:53:16-07:00 and 2018-11-20T11:14:20-07:00 refers to created at BETWEEN '2018-08-22T11:53:16-07:00' and '2018-11-20T11:14:20-07:00'

### SQL
SELECT DISTINCT T1.title FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.created_at BETWEEN '2018-08-22T11:53:16-07:00' AND '2018-11-20T11:14:20-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
To which categories do the podcasts of the reviewer whose id is EFB34EAC8E9397C belong?

### Hint
reviewer whose id is EFB34EAC8E9397C refers to author_id = 'EFB34EAC8E9397C'

### SQL
SELECT DISTINCT T1.category FROM categories AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.author_id = 'EFB34EAC8E9397C'
### 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
Indicate the slug and the itunes url of the podcast whose review content was written Can't stop listening.

### Hint
review content was written Can't stop listening refers to content = 'Can't stop listening'

### SQL
SELECT slug, itunes_url FROM podcasts WHERE podcast_id IN ( SELECT podcast_id FROM reviews WHERE content = 'Can''t stop listening' )
### 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 dates were the Don't Lie To Your Life Coach podcast reviews created?

### Hint
"Don't Lie To Your Life Coach" refers to title of podcast; date refers to created_at

### SQL
SELECT created_at FROM reviews WHERE podcast_id = ( SELECT podcast_id FROM podcasts WHERE title = 'Don''t Lie To Your Life Coach' )