WITH combine_2 AS
    (WITH combine AS
        (SELECT
            trip_id,
            duration_sec,
            start_date,
            start_station_name,
            member_gender,
            bikeshare_regions.name AS region
        FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips` AS bikeshare_trips
            INNER JOIN `bigquery-public-data.san_francisco_bikeshare.bikeshare_station_info` AS bikeshare_station_info
            ON bikeshare_trips.start_station_id = bikeshare_station_info.station_id
            INNER JOIN `bigquery-public-data.san_francisco_bikeshare.bikeshare_regions` AS bikeshare_regions
            ON bikeshare_station_info.region_id = bikeshare_regions.region_id
        WHERE
            bikeshare_trips.start_date BETWEEN '2014-01-01' AND '2017-01-01')
    SELECT
    *,
    RANK () OVER(PARTITION BY region ORDER BY start_date DESC) AS rnk
    FROM combine)
SELECT *
FROM combine_2
WHERE rnk < 2
ORDER BY region;