Database schema:

CREATE TABLE callcenterlogs (
	"Date received" DATE -- example: ['2017-03-23', '2017-03-27'],
	"Complaint ID" TEXT -- example: ['CR0000072', 'CR0000084'],
	"rand client" TEXT -- example: ['C00004587', 'C00003328'],
	phonefinal TEXT -- example: ['322-598-7152', '977-806-9726'],
	"vru+line" TEXT -- example: ['AA0103', 'AA0205'],
	call_id INTEGER -- example: ['34536', '34537'],
	priority INTEGER -- example: ['0', '2'],
	type TEXT -- example: ['NW', 'PS'],
	outcome TEXT -- example: ['HANG', 'AGENT'],
	server TEXT -- example: ['MICHAL', 'TOVA'],
	ser_start TEXT -- example: ['13:34:11', '10:58:22'],
	ser_exit TEXT -- example: ['11:16:10', '13:40:23'],
	ser_time TEXT -- example: ['00:17:48', '00:06:12'],
	PRIMARY KEY ("Complaint ID"), 
	CONSTRAINT fk_callcenterlogs_client FOREIGN KEY("rand client") REFERENCES client (client_id)
)


CREATE TABLE client (
	client_id TEXT -- example: ['C00000002', 'C00000001'],
	sex TEXT -- example: ['Male', 'Female'],
	day INTEGER -- example: ['4', '13'],
	month INTEGER -- example: ['2', '12'],
	year INTEGER -- example: ['1990', '1965'],
	age INTEGER -- example: ['54', '29'],
	social TEXT -- example: ['806-94-5725', '926-93-2157'],
	first TEXT -- example: ['Noah', 'Emma'],
	middle TEXT -- example: ['Avaya', 'Everest'],
	last TEXT -- example: ['Thompson', 'Smith'],
	phone TEXT -- example: ['212-423-7734', '367-171-6840'],
	email TEXT -- example: ['noah.thompson@gmail.com', 'emma.smith@gmail.com'],
	address_1 TEXT -- example: ['387 Wellington Ave.', '75 W. Berkshire St.'],
	address_2 TEXT -- example: ['Unit 6', 'Unit 1'],
	city TEXT -- example: ['New York City', 'Albuquerque'],
	state TEXT -- example: ['NY', 'NM'],
	zipcode INTEGER -- example: ['47246', '10040'],
	district_id INTEGER -- example: ['1', '18'],
	PRIMARY KEY (client_id), 
	CONSTRAINT fk_client_district FOREIGN KEY(district_id) REFERENCES district (district_id)
)


CREATE TABLE district (
	district_id INTEGER -- example: ['2', '1'],
	city TEXT -- example: ['New York City', 'Jacksonville'],
	state_abbrev TEXT -- example: ['NY', 'FL'],
	division TEXT -- example: ['South Atlantic', 'Middle Atlantic'],
	PRIMARY KEY (district_id), 
	CONSTRAINT fk_district_state FOREIGN KEY(state_abbrev) REFERENCES state ("StateCode")
)


CREATE TABLE events (
	"Date received" DATE -- example: ['2014-07-03', '2012-04-12'],
	"Product" TEXT -- example: ['Credit card', 'Bank account or service'],
	"Sub-product" TEXT -- example: ['Savings account', 'Checking account'],
	"Issue" TEXT -- example: ['Account opening, closing, or management', 'Deposits and withdrawals'],
	"Sub-issue" TEXT, 
	"Consumer complaint narrative" TEXT -- example: ["I called Eagle National Bank 's customer service l", 'Deposited a XXXX check into my account, BOFA is te'],
	"Tags" TEXT -- example: ['Older American, Servicemember', 'Older American'],
	"Consumer consent provided?" TEXT -- example: ['N/A', 'Consent not provided'],
	"Submitted via" TEXT -- example: ['Fax', 'Email'],
	"Date sent to company" TEXT -- example: ['2012-04-13', '2014-07-09'],
	"Company response to consumer" TEXT -- example: ['Closed with relief', 'Closed with explanation'],
	"Timely response?" TEXT -- example: ['No', 'Yes'],
	"Consumer disputed?" TEXT -- example: ['No', 'Yes'],
	"Complaint ID" TEXT -- example: ['CR0000072', 'CR0000084'],
	"Client_ID" TEXT -- example: ['C00001366', 'C00003714'],
	PRIMARY KEY ("Complaint ID", "Client_ID"), 
	CONSTRAINT fk_events_callcenterlogs FOREIGN KEY("Complaint ID") REFERENCES callcenterlogs ("Complaint ID"),
	CONSTRAINT fk_events_client FOREIGN KEY("Client_ID") REFERENCES client (client_id)
)


CREATE TABLE reviews (
	"Date" DATE -- example: ['2013-02-04', '2013-02-06'],
	"Stars" INTEGER -- example: ['5', '1'],
	"Reviews" TEXT -- example: ['Great job, Eagle National! Each person was profess', 'Matthew Richardson is professional and helpful. He'],
	"Product" TEXT -- example: ['Eagle National Mortgage', 'Eagle National Bank'],
	district_id INTEGER -- example: ['65', '66'],
	PRIMARY KEY ("Date"), 
	CONSTRAINT fk_reviews_district FOREIGN KEY(district_id) REFERENCES district (district_id)
)


CREATE TABLE state (
	"StateCode" TEXT -- example: ['AL', 'AR'],
	"State" TEXT -- example: ['Arkansas', 'Alabama'],
	"Region" TEXT -- example: ['West', 'South'],
	PRIMARY KEY ("StateCode")
)

Question: Calculate the average number of complaints received from New Bedford each year which are closed with explanation.
Evidence: average = AVG("Complaint ID"); New Bedford refers to city = 'New Bedford'; closed with explanation refers to Company response to consumer = 'Closed with explanation';

SQL:
SELECT STRFTIME('%Y', T3.`Date received`) , CAST(SUM(CASE WHEN T3.`Company response to consumer` = 'Closed with explanation' THEN 1 ELSE 0 END) AS REAL) / COUNT(T3.`Complaint ID`) AS average FROM callcenterlogs AS T1 INNER JOIN client AS T2 ON T1.`rand client` = T2.client_id INNER JOIN events AS T3 ON T1.`Complaint ID` = T3.`Complaint ID` WHERE T2.city = 'New Bedford' GROUP BY strftime('%Y', T3.`Date received`)