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 total trip duration made within Palo Alto city? Convert the duration to hour.

### Hint
total trip duration to hour = DIVIDE(SUM(duration), 3600);

### SQL
SELECT CAST(SUM(T1.duration) AS REAL) / 3600 FROM trip AS T1 LEFT JOIN station AS T2 ON T2.name = T1.start_station_name WHERE T2.city = 'Palo Alto'
### 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 bicycle is the least used bike. Check if the start and end station are from the same city and calculate the total duration travelled by the bicycle in hours for a trip made within the same city.

### Hint
least used bike refers to bike_id with MIN(COUNT(main_trip.id)); start station refers to start_station_name; end station refers to end_station_name; total duration in hour = DIVIDE(duration, 3600) AS hour;

### SQL
SELECT T2.bike_id, T2.start_station_name, T2.end_station_name, T1.city , CAST(T2.duration AS REAL) / 3600 FROM station AS T1 INNER JOIN trip AS T2 ON T1.name = T2.start_station_name GROUP BY T2.bike_id ORDER BY COUNT(T2.id) 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
Count the number of subscribers who started their trips in Market at 4th.

### Hint
subsscriber refers to subscription_type = subscriber; started their trips in refers to start_station_name; start_station_name = 'Market at 4th';

### SQL
SELECT COUNT(CASE WHEN subscription_type = 'Subscriber' AND start_station_name = 'Market at 4th' THEN id END) FROM trip
### 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 the names of the stations within Mountain View that were installed on 12/31/2013.

### Hint
Mountain View refers to city = 'Mountain View'; installed on 12/31/2013 refers to installation_date = '12/31/2013';

### SQL
SELECT name FROM station WHERE installation_date = '12/31/2013' AND city = 'Mountain View'
### 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 city is Townsend at 7th Station located and how many bikes could it hold?

### Hint
Townsend at 7th Station refers to city = 'Townsend at 7th Station'; number of bikes a station can hold refers to SUM(dock_count);

### SQL
SELECT city, SUM(dock_count) FROM station WHERE name = 'Townsend at 7th'
### 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 bikes could Evelyn Park and Ride hold and how many users who started on that station are subscribers?

### Hint
number of bikes a station can hold refers to SUM(dock_count); Evelyn Park and Ride refers to name = 'Evelyn Park and Ride'; started on the station refers to start_station_name; subscribers refers to subscription_type = 'subscriber';

### SQL
SELECT SUM(T2.dock_count), COUNT(T1.subscription_type) FROM trip AS T1 INNER JOIN station AS T2 ON T2.name = T1.start_station_name WHERE T2.name = 'Evelyn Park and Ride' AND T1.start_station_name = T2.name 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
How many subscribers are in the zip code of 94301 and what is the hottest temperature recorded on that zip code?

### Hint
hottest temperature refers to MAX(max_temperature_f);

### SQL
SELECT COUNT(T3.zip_code), T3.max_temperature_f FROM trip AS T2 INNER JOIN weather AS T3 ON T3.zip_code = T2.zip_code WHERE T3.zip_code = 94301 AND T2.subscription_type = 'Subscriber' ORDER BY T3.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 percentage ration of customers to subscribers that started their trips within the city of San Francisco?

### Hint
customer refers to subscription_type = 'customer'; subscriber refers to subscription_type = 'subscriber'; started their trips within refers to start_station_id; percentage ratio = DIVIDE(SUM(subscription_type = 'Customer'), SUM(subscription_type = 'Subscriber')) as percentage;

### SQL
SELECT CAST(SUM(CASE WHEN T1.subscription_type = 'Customer' THEN 1 ELSE 0 END) AS REAL) * 100 / SUM(CASE WHEN T1.subscription_type = 'Subscriber' THEN 1 ELSE 0 END) FROM trip AS T1 LEFT JOIN station AS T2 ON T2.name = T1.start_station_name WHERE T2.city = 'San Francisco'
### 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 trips for the starting station of Santa Clara at Almaden and what is the latitude and longitude of this station?

### Hint
average duration = DIVIDE(SUM(duration), COUNT(id)); starting station refers to start_station_name; start_station_name = 'Santa Clara at Almaden'; latitude refers to lat; longitude refers to long;

### SQL
SELECT AVG(T1.duration), T2.lat, T2.long FROM trip AS T1 LEFT JOIN station AS T2 ON T2.name = T1.start_station_name LEFT JOIN station AS T3 ON T3.name = T1.end_station_name WHERE T1.start_station_name = 'Santa Clara at Almaden'
### 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 shortest trip made starting from Franklin at Maple and what is the maximum wind speed at that date?

### Hint
shortest trip refers to MIN(duration); starting from refers to start_station_name; start_station_name = 'Franklin at Maple'; maximum wind speed refers to max_wind_Speed_mph;

### SQL
SELECT MIN(T1.duration), MAX(T2.max_wind_Speed_mph) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T1.start_station_name = 'Franklin at Maple' AND T2.date = '9/4/2013'