lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
What is the location coordinates of the bike station from which the bike for the trip that last the longest was borrowed?

### Hint
location coordinates refers to (lat, long); bike that was borrowed the longest refers to MAX(duration);

### SQL
SELECT T2.lat, T2.long FROM trip AS T1 INNER JOIN station AS T2 ON T2.name = T1.start_station_name WHERE T1.duration = ( SELECT MAX(T1.duration) FROM trip AS T1 INNER JOIN station AS T2 ON T2.name = T1.start_station_name )
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
How many docks were left at the end station for trip ID4069?

### Hint
end station refers to end_station_id; docks that were left refers to docks_available;

### SQL
SELECT SUM(T2.docks_available) FROM trip AS T1 INNER JOIN status AS T2 ON T2.station_id = T1.end_station_id WHERE T1.ID = 4069
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
Among the bike trips started on the days with a fog in 2013, how many of those trips started from the station "2nd at Townsend"?

### Hint
started on the days with a fog refers to start_date where events = 'fog'; in 2013 refers to date LIKE '%2013'; started from station refers to start_station_name; start_station_name = '2nd at Townsend';

### SQL
SELECT COUNT(T1.start_station_name) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T2.date LIKE '%2013%' AND T2.events = 'Fog' AND T1.start_station_name = '2nd at Townsend' AND T2.zip_code = 94107
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
What is the longest duration for a bike trip starting on a day with a fog in 2013?

### Hint
longest duration refers to MAX(duration); starting on a day with a fog refers to start_date where events = 'fog'; in 2013 refers to date LIKE '%2013';

### SQL
SELECT MAX(T1.duration) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T2.date LIKE '%2013%' AND T2.events = 'Fog' AND T2.zip_code = 94107
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
When was the bike station from which the bike was borrowed on trip ID4069 installed?

### Hint
bike was borrowed from refers to start_station_id; when the bike station was installed refers to installation_date;

### SQL
SELECT T2.installation_date FROM trip AS T1 INNER JOIN station AS T2 ON T2.name = T1.start_station_name WHERE T1.id = 4069
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
How many trips with a bike borrowed from the stations in San Francisco were made by a subscriber?

### Hint
bike was borrowed from refers to start_station_id; San Francisco refers to city = 'San Francisco'; subscriber refers to subscription_type = 'Subscriber';

### SQL
SELECT COUNT(T1.id) FROM trip AS T1 INNER JOIN station AS T2 ON T2.ID = T1.start_station_id WHERE T2.city = 'San Francisco' AND T1.subscription_type = 'Subscriber'
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
On the day with the hottest temperature ever in 2014, how many bike trips started from the station 2nd at Folsom?

### Hint
hottest temperature refers to max_temperatutre_f; in 2014 refers to date LIKE '%2014'; started from station refers to start_station_name; start_station_name = '2nd at Folsom';

### SQL
SELECT COUNT(T1.start_station_name) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T2.date LIKE '%2014%' AND T2.zip_code = 94107 AND T1.start_station_name = '2nd at Folsom' ORDER BY T2.max_temperature_f DESC LIMIT 1
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
What is the average duration of a bike trip made on the day with the hottest temperature ever in 2014?

### Hint
average duration = DIVIDE(SUM(duration), COUNT(id)); hottest temperature refers to max_temperature_f; in 2014 refers to date LIKE '%2014';

### SQL
SELECT AVG(T1.duration) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T2.date LIKE '%2014%' AND T1.start_station_name = '2nd at Folsom' AND T2.max_temperature_f = ( SELECT max_temperature_f FROM weather ORDER BY max_temperature_f DESC LIMIT 1 )
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
List out all end stations for a bicycle that were making a trip starting from 2nd at South Park station? Only retain the unique value.

### Hint
end station refers to end_station_name; starting from refers to start_station_name; start_station_name = '2nd at South Park';

### SQL
SELECT DISTINCT end_station_name FROM trip WHERE start_station_name = '2nd at South Park'
### Database Schema
CREATE TABLE "station"
(
    id                INTEGER not null
        primary key,
    name              TEXT,
    lat               REAL,
    long              REAL,
    dock_count        INTEGER,
    city              TEXT,
    installation_date TEXT
)

CREATE TABLE "status"
(
    station_id      INTEGER,
    bikes_available INTEGER,
    docks_available INTEGER,
    time            TEXT
)

CREATE TABLE "trip"
(
    id                 INTEGER not null
        primary key,
    duration           INTEGER,
    start_date         TEXT,
    start_station_name TEXT,
    start_station_id   INTEGER,
    end_date           TEXT,
    end_station_name   TEXT,
    end_station_id     INTEGER,
    bike_id            INTEGER,
    subscription_type  TEXT,
    zip_code           INTEGER
)

CREATE TABLE "weather"
(
    date                           TEXT,
    max_temperature_f              INTEGER,
    mean_temperature_f             INTEGER,
    min_temperature_f              INTEGER,
    max_dew_point_f                INTEGER,
    mean_dew_point_f               INTEGER,
    min_dew_point_f                INTEGER,
    max_humidity                   INTEGER,
    mean_humidity                  INTEGER,
    min_humidity                   INTEGER,
    max_sea_level_pressure_inches  REAL,
    mean_sea_level_pressure_inches REAL,
    min_sea_level_pressure_inches  REAL,
    max_visibility_miles           INTEGER,
    mean_visibility_miles          INTEGER,
    min_visibility_miles           INTEGER,
    max_wind_Speed_mph             INTEGER,
    mean_wind_speed_mph            INTEGER,
    max_gust_speed_mph             INTEGER,
    precipitation_inches           TEXT,
    cloud_cover                    INTEGER,
    events                         TEXT,
    wind_dir_degrees               INTEGER,
    zip_code                       TEXT
)

### Question
How many rainy days were recorded in Mountain View?

### Hint
rainy days refers to events = 'rain'; Mountain View refers to zip_code = 94041;

### SQL
SELECT SUM(IIF(zip_code = 94041 AND events = 'Rain', 1, 0)) FROM weather