Database schema:

CREATE TABLE station (
	id INTEGER NOT NULL -- example: ['2', '3'],
	name TEXT -- example: ['San Jose Diridon Caltrain Station', 'San Jose Civic Center'],
	lat REAL -- example: ['37.329732', '37.330698'],
	long REAL -- example: ['-121.90178200000001', '-121.888979'],
	dock_count INTEGER -- example: ['15', '27'],
	city TEXT -- example: ['Redwood City', 'San Jose'],
	installation_date TEXT -- example: ['8/6/2013', '8/5/2013'],
	PRIMARY KEY (id)
)


CREATE TABLE status (
	station_id INTEGER -- example: ['2', '3'],
	bikes_available INTEGER -- example: ['2', '3'],
	docks_available INTEGER -- example: ['24', '25'],
	time TEXT -- example: ['2013/08/29 12:06:01', '2013/08/29 12:07:01']
)


CREATE TABLE trip (
	id INTEGER NOT NULL -- example: ['4073', '4069'],
	duration INTEGER -- example: ['174', '1067'],
	start_date TEXT -- example: ['8/29/2013 9:08', '8/29/2013 9:24'],
	start_station_name TEXT -- example: ['South Van Ness at Market', '2nd at South Park'],
	start_station_id INTEGER -- example: ['64', '66'],
	end_date TEXT -- example: ['8/29/2013 9:11', '8/29/2013 9:42'],
	end_station_name TEXT -- example: ['San Francisco Caltrain 2 (330 Townsend)', '2nd at South Park'],
	end_station_id INTEGER -- example: ['64', '69'],
	bike_id INTEGER -- example: ['288', '321'],
	subscription_type TEXT -- example: ['Customer', 'Subscriber'],
	zip_code INTEGER -- example: ['94114', '94703'],
	PRIMARY KEY (id)
)


CREATE TABLE weather (
	date TEXT -- example: ['8/30/2013', '8/29/2013'],
	max_temperature_f INTEGER -- example: ['78', '74'],
	mean_temperature_f INTEGER -- example: ['68', '69'],
	min_temperature_f INTEGER -- example: ['60', '61'],
	max_dew_point_f INTEGER -- example: ['61', '57'],
	mean_dew_point_f INTEGER -- example: ['58', '56'],
	min_dew_point_f INTEGER -- example: ['54', '56'],
	max_humidity INTEGER -- example: ['93', '90'],
	mean_humidity INTEGER -- example: ['75', '70'],
	min_humidity INTEGER -- example: ['50', '57'],
	max_sea_level_pressure_inches REAL -- example: ['30.05', '30.07'],
	mean_sea_level_pressure_inches REAL -- example: ['30.02', '30.0'],
	min_sea_level_pressure_inches REAL -- example: ['29.93', '29.97'],
	max_visibility_miles INTEGER -- example: ['9', '10'],
	mean_visibility_miles INTEGER -- example: ['9', '10'],
	min_visibility_miles INTEGER -- example: ['7', '10'],
	"max_wind_Speed_mph" INTEGER -- example: ['23', '29'],
	mean_wind_speed_mph INTEGER -- example: ['11', '13'],
	max_gust_speed_mph INTEGER -- example: ['28', '35'],
	precipitation_inches TEXT -- example: ['0', '0.23'],
	cloud_cover INTEGER -- example: ['2', '4'],
	events TEXT -- example: ['', 'Fog'],
	wind_dir_degrees INTEGER -- example: ['286', '291'],
	zip_code TEXT -- example: ['94063', '94107']
)

Question: Count the number of subscribers who started their trips in Market at 4th.
Evidence: 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