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
On 8/29/2013, who took the longest to arrive in California Ave Caltrain Station from University and Emerson? Indicate the bike id.

### Hint
start_date = '8/29/2013'; end_date = '8/29/2013'; end_station_name = 'California Ave Caltrain Station'; start_station_name = 'University and Emerson'; who took the longest to arrive refers to MAX(duration);

### SQL
SELECT bike_id FROM trip WHERE start_date LIKE '8/29/2013%' AND end_date LIKE '8/29/2013%' AND end_station_name = 'California Ave Caltrain Station' AND start_station_name = 'University and Emerson' AND duration = ( SELECT MAX(duration) FROM trip WHERE start_date LIKE '8/29/2013%' AND end_date LIKE '8/29/2013%' AND end_station_name = 'California Ave Caltrain Station' AND start_station_name = 'University and Emerson' )
### 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 stations in San Francico can hold more than 20 bikes?

### Hint
San Francico is the name of the city; can hold more than 20 bikes implies dock's capacity and refers to dock_count≥20;

### SQL
SELECT SUM(CASE WHEN city = 'San Francisco' AND dock_count > 20 THEN 1 ELSE 0 END) FROM station
### 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 hottest temperature recorded? If there are multiple dates with the hottest temperature, indicate all of the dates.

### Hint
the hottest temperature refers to max_temperature_f;

### SQL
SELECT max_temperature_f, date FROM weather WHERE max_temperature_f = ( SELECT MAX(max_temperature_f) FROM weather WHERE max_temperature_f IS NOT NULL AND max_temperature_f IS NOT '' )
### 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 maximum dew point in Fahrenheit degree on 7/15/2014 in the area with a zip code of 94301?

### Hint
the maximum dew point in Fahrenheit degree refers to max_dew_point_f; date = '7/15/2014';

### SQL
SELECT DISTINCT CASE WHEN date = '7/15/2014' AND zip_code = 94301 THEN max_dew_point_f END FROM weather
### 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
Which year experienced the most rain?

### Hint
events = 'Rain'; year refers to YEAR(date);

### SQL
SELECT SUBSTR(CAST(date AS TEXT), -4) FROM weather GROUP BY SUBSTR(CAST(date AS TEXT), -4) ORDER BY SUM(CASE WHEN events LIKE '%Rain%' OR events LIKE '%rain%' THEN 1 ELSE 0 END) 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
On 10/20/2014, what is the duration of the fastest trip which started from the station with latitude and longitudes of 37.789625 and -122.400811, respectively? Indicate the bike id.

### Hint
lat = '37.789625' and long = '-122.400811' are latitude and longitude coordinates indicating location; started from the station refers to start_station_name; start_date = '10/20/2014'; duration of the fastest trip refers to MIN(duration);

### SQL
SELECT MIN(T2.duration), T2.bike_id FROM station AS T1 INNER JOIN trip AS T2 ON T2.start_station_name = T1.name WHERE T2.start_date LIKE '10/20/2014%' AND T1.lat = 37.789625 AND T1.long = -122.400811
### 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 subscribers who rented a bike from South Van Ness at Market on 12/1/2013, whose duration was the shortest and to which station was the bike returned to? Indicate South Van Ness's dock count.

### Hint
South Van Ness at Market refers to start_station_name; subscriber refers to subscription_type; start_date = '12/1/2013'; the shortest duration refers to MIN(duration); which station was the bike returned to refers to end_station_name;

### SQL
SELECT MIN(T2.duration), T2.end_station_name, COUNT(T2.start_station_name) FROM station AS T1 INNER JOIN trip AS T2 ON T2.start_station_name = T1.name WHERE T2.start_date LIKE '12/1/2013%' AND T2.start_station_name = 'South Van Ness at Market' AND T2.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
What is the maximum humidity in Powell Street BART when bike 496 was borrowed from the station on 8/29/2013?

### Hint
Powell Street refers to start_station_name; bike 496 refers to bike_id = '496'; start_date = '8/29/2013';

### SQL
SELECT T2.max_humidity FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T1.start_date LIKE '8/29/2013%' AND T1.bike_id = 496 AND T1.start_station_name = 'Powell Street BART'
### 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
Which day in the month of November, 2014 have a foggy weather in the zip code 94301 and in total, how many bikes were borrowed by subscribers from all of the stations in the said day?

### Hint
day in the month of November, 2014 refers to start_date between '11/1/2014' and '11/30/2014'; foggy weather refers to events = 'Fog'; subscriber refers to subscription_type; all of the stations bikes were borrowed from refer to start_station_name;

### SQL
SELECT T2.date, 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 '11/%/2014%' AND T2.zip_code = 94301 AND T2.events = 'Fog' 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
What is the name of the station that is less used by customers who borrow bikes from? Indicate when was the station installed.

### Hint
less used station where bikes are borrowed from refers to start_station_name which has the least number of customers; subscription_type = 'Customer'; when installed refers to installation_date;

### SQL
SELECT T1.start_station_name, T2.installation_date FROM trip AS T1 INNER JOIN station AS T2 ON T2.name = T1.start_station_name WHERE T1.subscription_type = 'Customer' GROUP BY T1.start_station_name ORDER BY COUNT(T1.subscription_type) LIMIT 1