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
How many podcasts are there in the category which has the most podcasts?

### Hint
category which has the most podcast refers to the category with Max(count(podcast_id))

### SQL
SELECT COUNT(podcast_id) FROM categories WHERE category = ( SELECT category FROM categories GROUP BY category ORDER BY COUNT(podcast_id) DESC 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
What is the percentage of the podcast that are categorized in four or more categories?

### Hint
categorized in 4 or more refers to Count(category) > 4; percentage = Divide(Count(podcast_id(count(category) > 4)), Count(podcast_id)) * 100

### SQL
SELECT COUNT(T1.podcast_id) FROM ( SELECT podcast_id FROM categories GROUP BY podcast_id HAVING COUNT(category) >= 4 ) AS T1
### 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
Provide the itunes id and url for podcast titled 'Brown Suga Diaries'.

### Hint
url refers to itunes_url; 'Brown Suga Diaries' is the title of podcast

### SQL
SELECT itunes_id, itunes_url FROM podcasts WHERE title = 'Brown Suga Diaries'
### 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 podcast with its itunes url for all title containing the word 'Dream'.

### Hint
containing the word 'Dream' refers to title LIKE '%Dream%'

### SQL
SELECT itunes_url FROM podcasts WHERE title LIKE '%Dream%' GROUP BY itunes_url
### 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
Name all the categories for podcast titled 'I Heart My Life Show'.

### Hint
'I Hearty My Life Show' is the title of podcast

### SQL
SELECT T1.category FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.title = 'I Heart My Life Show'
### 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 podcast title and its itunes url under the 'society-culture' category.

### SQL
SELECT T2.title, T2.itunes_url FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.category = 'society-culture'
### 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 people rated 5 for the podcast which title contains the word 'spoiler' under the 'art' category '?

### Hint
rated 5 refers to rating = 5; contain the word 'spoilers' refers to title like '%spoilers%'; 'art' is the category name;

### SQL
SELECT COUNT(T3.podcast_id) 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 LIKE '%spoilers%' AND T1.category = 'arts' AND T3.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
List the authors who created review for podcast titled 'Pop Rocket' in 2016 with rating less than 5.

### Hint
Pop Rocket' is the title of podcast; in 2016 refers to created_at like'2016%'; rating less than 5 refers to rating < 5; author 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.title = 'Pop Rocket' AND T2.created_at LIKE '2016-%' 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
Name all the podcast title and its category with average rating of more than 3.0.

### Hint
average rating of more than 3.0 refers to avg(rating) > 3.0

### SQL
SELECT T2.title, 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 GROUP BY T3.podcast_id HAVING AVG(T3.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
List all content reviewed for podcast with the best rating under the 'fiction' category. State the podcast title.

### Hint
'fiction' is the category name; best rating refers to rating = 5; content reviewed refers to content

### SQL
SELECT DISTINCT T2.title 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 T3.rating = 5 AND 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
State the podcast title, content review and rating for all reviews with titled 'Love it!'

### Hint
"Love it!" is the title of review; content reviewed refers to content

### SQL
SELECT DISTINCT T1.title, T2.content, T2.rating FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.title = 'Love it!'
### 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
Find the author, rating and review creation date of review for podcast title 'In The Thick'.

### Hint
"In The Thick" is the title of podcast; author refers to author_id; creation date refers to created_at

### SQL
SELECT T2.author_id, T2.rating, T2.created_at FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'In The Thick' GROUP BY T2.author_id, T2.rating, T2.created_at
### 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 podcast was reviewed the latest? State the date of creation, podcast tile and rating.

### Hint
latest refers to Max(created_at); date of creation refers to created_at

### SQL
SELECT T1.podcast_id, T2.created_at, T2.title, T2.rating FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id ORDER BY T2.created_at DESC 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
Name the podcast title, rating and review content created by '76A4C24B6038145'.

### Hint
"76A4C24B6038145" is author_id; review content refers to content

### SQL
SELECT T2.title, T2.rating, T2.content FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.author_id = '76A4C24B6038145'
### 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
For all reviews with the worst rating, state the podcast title as well as the review title and content.

### Hint
worst rating refers to rating = 1

### SQL
SELECT DISTINCT T1.title, T2.title, T2.content FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.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
List all reviews created in May 2019. State the title of podcast and review rating.

### Hint
created in May 2019 refers to created_at like '2019-05%'

### SQL
SELECT DISTINCT T1.title, T2.rating FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.created_at LIKE '2019-05-%'
### 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 podcast that is most reviewed?

### Hint
most reviewed refers to Max(Count(reviews.podcast_id)); average rating refers to AVG (rating)

### SQL
SELECT AVG(T2.rating) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id GROUP BY T1.podcast_id ORDER BY COUNT(T2.content) DESC 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
Which category does the podcast titled 'SciFi Tech Talk' belong to?

### Hint
podcast titled 'SciFi Tech Talk' refers to title = 'SciFi Tech Talk'

### SQL
SELECT T1.category FROM categories AS T1 INNER JOIN podcasts AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.title = 'SciFi Tech Talk'
### 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 name of the podcast in which a commentor left a comment with the title 'Long time listener, calling it quits?' Include the URL of the podcast as well.

### Hint
comment refers to review; 'Long time listener, calling it quits' is the title of review;  name of the podcast refers to title of podcast; URL refers to itunes_url

### SQL
SELECT podcast_id, itunes_url FROM podcasts WHERE podcast_id = ( SELECT podcast_id FROM reviews WHERE title = 'Long time listener, calling it quits' )