Database schema:

CREATE TABLE city (
	city_id INTEGER -- example: ['101', '100'],
	city_name TEXT -- example: ['Union City', 'Huntington Park'],
	state TEXT -- example: ['New Jersey', 'California'],
	population INTEGER -- example: ['61348', '67088'],
	area REAL -- example: ['1.3', '3.0'],
	PRIMARY KEY (city_id)
)


CREATE TABLE customer (
	cust_id INTEGER -- example: ['304', '193'],
	cust_name TEXT -- example: ["Pard's Trailer Sales", 'Advanced Fabricators'],
	annual_revenue INTEGER -- example: ['17158109', '39588651'],
	cust_type TEXT -- example: ['manufacturer', 'wholesaler'],
	address TEXT -- example: ['5910 South 300 West', '5141 Summit Boulevard'],
	city TEXT -- example: ['West Palm Beach', 'Salt Lake City'],
	state TEXT -- example: ['UT', 'FL'],
	zip REAL -- example: ['84107.0', '33415.0'],
	phone TEXT -- example: ['(561) 683-3535', '(801) 262-4864'],
	PRIMARY KEY (cust_id)
)


CREATE TABLE driver (
	driver_id INTEGER -- example: ['20', '21'],
	first_name TEXT -- example: ['Andrea', 'Sue'],
	last_name TEXT -- example: ['Simons', 'Newell'],
	address TEXT -- example: ['3574 Oak Limb Cv', '268 Richmond Ave'],
	city TEXT -- example: ['Memphis'],
	state TEXT -- example: ['TN'],
	zip_code INTEGER -- example: ['38135', '38106'],
	phone TEXT -- example: ['(901) 774-6569', '(901) 384-0984'],
	PRIMARY KEY (driver_id)
)


CREATE TABLE shipment (
	ship_id INTEGER -- example: ['1000', '1001'],
	cust_id INTEGER -- example: ['3660', '2001'],
	weight REAL -- example: ['3528.0', '11394.0'],
	truck_id INTEGER -- example: ['2', '1'],
	driver_id INTEGER -- example: ['23', '27'],
	city_id INTEGER -- example: ['186', '137'],
	ship_date TEXT -- example: ['2016-01-18', '2016-01-08'],
	PRIMARY KEY (ship_id), 
	CONSTRAINT fk_shipment_customer FOREIGN KEY(cust_id) REFERENCES customer (cust_id),
	CONSTRAINT fk_shipment_city FOREIGN KEY(city_id) REFERENCES city (city_id),
	CONSTRAINT fk_shipment_driver FOREIGN KEY(driver_id) REFERENCES driver (driver_id),
	CONSTRAINT fk_shipment_truck FOREIGN KEY(truck_id) REFERENCES truck (truck_id)
)


CREATE TABLE truck (
	truck_id INTEGER -- example: ['2', '1'],
	make TEXT -- example: ['Mack', 'Peterbilt'],
	model_year INTEGER -- example: ['2005', '2006'],
	PRIMARY KEY (truck_id)
)

Question: How many customers who live in California that are retailers?
Evidence: "retailer" is the cust_type; live in California refers to state = 'CA'

SQL:
SELECT COUNT(*) FROM customer WHERE cust_type = 'retailer' AND state = 'CA'