Database schema:

CREATE TABLE all_prevalences (
	"ITEM" TEXT -- example: ['0.65 Ml Depo Subq Provera 160 Mg/Ml Prefilled Syri', '0.3 Ml Epinephrine 0.5 Mg/Ml Auto Injector'],
	"POPULATION TYPE" TEXT -- example: ['LIVING'],
	"OCCURRENCES" INTEGER -- example: ['487', '868'],
	"POPULATION COUNT" INTEGER -- example: ['1000'],
	"PREVALENCE RATE" REAL -- example: ['0.487', '0.868'],
	"PREVALENCE PERCENTAGE" REAL -- example: ['48.7', '86.8'],
	PRIMARY KEY ("ITEM")
)


CREATE TABLE allergies (
	"START" TEXT -- example: ['3/11/95', '6/15/58'],
	"STOP" TEXT -- example: ['12/22/14', '4/21/10'],
	"PATIENT" TEXT -- example: ['01347a8e-3a92-4f1e-99c9-5d6df0db2331', '00341a88-1cc1-4b39-b0f9-05b0531991a0'],
	"ENCOUNTER" TEXT -- example: ['ddc8a625-07e6-4a02-a616-e0ce07b5f305', '84714823-ee2b-42ba-b79b-6043270cfdc7'],
	"CODE" INTEGER -- example: ['300916003', '232347008'],
	"DESCRIPTION" TEXT -- example: ['Allergy to dairy product', 'Allergy to tree pollen'],
	PRIMARY KEY ("PATIENT", "ENCOUNTER", "CODE"), 
	CONSTRAINT fk_allergies_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_allergies_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)


CREATE TABLE careplans (
	"ID" TEXT -- example: ['e031962d-d13d-4ede-a449-040417d5e4fb', '26b879b7-0066-4bd6-b59c-099909720155'],
	"START" DATE -- example: ['2010-10-16', '2009-01-11'],
	"STOP" DATE -- example: ['2009-04-07', '2010-10-23'],
	"PATIENT" TEXT -- example: ['71949668-1c2e-43ae-ab0a-64654608defb', 'c2caaace-9119-4b2d-a2c3-4040f5a9cf32'],
	"ENCOUNTER" TEXT -- example: ['bed7ecff-b41c-422b-beac-ea00c8b02837', '4d451e22-a354-40c9-8b33-b6126158666d'],
	"CODE" REAL -- example: ['53950000.0', '304510005.0'],
	"DESCRIPTION" TEXT -- example: ['Respiratory therapy', 'Recommendation to avoid exercise'],
	"REASONCODE" INTEGER -- example: ['38822007', '10509002'],
	"REASONDESCRIPTION" TEXT -- example: ['Acute bronchitis (disorder)', 'Cystitis'],
	CONSTRAINT fk_careplans_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_careplans_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)


CREATE TABLE claims (
	"ID" TEXT -- example: ['000acf3a-fcac-4cab-ac41-0153802bfd8c', '0004caaf-fd7a-4f64-bda6-dce5ebd1196c'],
	"PATIENT" TEXT -- example: ['71949668-1c2e-43ae-ab0a-64654608defb', 'c2caaace-9119-4b2d-a2c3-4040f5a9cf32'],
	"BILLABLEPERIOD" DATE -- example: ['2008-03-11', '2009-01-11'],
	"ORGANIZATION" TEXT -- example: ['temp organization'],
	"ENCOUNTER" TEXT -- example: ['71949668-1c2e-43ae-ab0a-64654608defb', 'c2caaace-9119-4b2d-a2c3-4040f5a9cf32'],
	"DIAGNOSIS" TEXT, 
	"TOTAL" INTEGER -- example: ['100'],
	PRIMARY KEY ("ID"), 
	CONSTRAINT fk_claims_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_claims_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)


CREATE TABLE conditions (
	"START" DATE -- example: ['2010-10-16', '2009-01-08'],
	"STOP" DATE -- example: ['2009-01-21', '2010-10-23'],
	"PATIENT" TEXT -- example: ['71949668-1c2e-43ae-ab0a-64654608defb', 'c2caaace-9119-4b2d-a2c3-4040f5a9cf32'],
	"ENCOUNTER" TEXT -- example: ['bed7ecff-b41c-422b-beac-ea00c8b02837', '4d451e22-a354-40c9-8b33-b6126158666d'],
	"CODE" INTEGER -- example: ['38822007', '10509002'],
	"DESCRIPTION" TEXT -- example: ['Acute bronchitis (disorder)', 'Cystitis'],
	CONSTRAINT fk_conditions_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_conditions_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient),
	CONSTRAINT fk_conditions_all_prevalences FOREIGN KEY("DESCRIPTION") REFERENCES all_prevalences ("ITEM")
)


CREATE TABLE encounters (
	"ID" TEXT -- example: ['000c20ea-5c3d-43a3-9608-bd37c22f13c8', '0013c51f-c315-4112-ad5e-466b604a05ed'],
	"DATE" DATE -- example: ['2008-03-11', '2009-01-11'],
	"PATIENT" TEXT -- example: ['71949668-1c2e-43ae-ab0a-64654608defb', 'c2caaace-9119-4b2d-a2c3-4040f5a9cf32'],
	"CODE" INTEGER -- example: ['185345009', '185349003'],
	"DESCRIPTION" TEXT -- example: ['Outpatient Encounter', 'Encounter for symptom'],
	"REASONCODE" INTEGER -- example: ['38822007', '10509002'],
	"REASONDESCRIPTION" TEXT -- example: ['Acute bronchitis (disorder)', 'Cystitis'],
	PRIMARY KEY ("ID"), 
	CONSTRAINT fk_encounters_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)


CREATE TABLE immunizations (
	"DATE" DATE -- example: ['2007-11-03', '2007-11-02'],
	"PATIENT" TEXT -- example: ['87828e93-e09b-435b-be8e-657361f1c68a', '70cbc79e-dd9d-4e3b-8158-a92d85951f94'],
	"ENCOUNTER" TEXT -- example: ['898ab2de-8a71-4fb3-b239-b768b915284a', 'fc8e3271-cc7c-4169-8c99-d46635a86478'],
	"CODE" INTEGER -- example: ['62', '8'],
	"DESCRIPTION" TEXT -- example: ['Td (adult) preservative free', 'Influenza  seasonal  injectable  preservative free'],
	PRIMARY KEY ("DATE", "PATIENT", "ENCOUNTER", "CODE"), 
	CONSTRAINT fk_immunizations_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_immunizations_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)


CREATE TABLE medications (
	"START" DATE -- example: ['1918-04-17', '1919-06-28'],
	"STOP" DATE -- example: ['2009-01-21', '2008-06-04'],
	"PATIENT" TEXT -- example: ['a9c61b1f-3ad5-47e0-9aae-1613640a8187', 'ce11bcba-c83c-43ae-802d-b20ee8715afe'],
	"ENCOUNTER" TEXT -- example: ['23ab74d3-044b-465b-ad85-394acfda0ef7', '6334071d-e6b0-42c1-a082-2a08e123de4e'],
	"CODE" INTEGER -- example: ['745679', '834060'],
	"DESCRIPTION" TEXT -- example: ['Penicillin V Potassium 250 MG', 'NuvaRing 0.12/0.015 MG per 24HR 21 Day Vaginal Rin'],
	"REASONCODE" INTEGER -- example: ['10509002', '43878008'],
	"REASONDESCRIPTION" TEXT -- example: ['Acute bronchitis (disorder)', 'Streptococcal sore throat (disorder)'],
	PRIMARY KEY ("START", "PATIENT", "ENCOUNTER", "CODE"), 
	CONSTRAINT fk_medications_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_medications_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)


CREATE TABLE observations (
	"DATE" DATE -- example: ['2008-03-11', '2009-04-07'],
	"PATIENT" TEXT -- example: ['71949668-1c2e-43ae-ab0a-64654608defb', 'c2caaace-9119-4b2d-a2c3-4040f5a9cf32'],
	"ENCOUNTER" TEXT -- example: ['5114a5b4-64b8-47b2-82a6-0ce24aae0943', 'bdb926b8-5b6d-4366-bbe9-76fe3f3fbc4f'],
	"CODE" TEXT -- example: ['29463-7', '8302-2'],
	"DESCRIPTION" TEXT -- example: ['Body Height', 'Body Weight'],
	"VALUE" REAL -- example: ['166.03', '54.42'],
	"UNITS" TEXT -- example: ['cm', 'kg'],
	CONSTRAINT fk_observations_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_observations_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)


CREATE TABLE patients (
	patient TEXT -- example: ['00269bb7-e3ab-43a9-9cdf-cdf9b6e3b2b3', '00341a88-1cc1-4b39-b0f9-05b0531991a0'],
	birthdate DATE -- example: ['2016-12-15', '1929-04-08'],
	deathdate DATE -- example: ['2029-11-11', '2020-02-19'],
	ssn TEXT -- example: ['999-78-5976', '999-59-9186'],
	drivers TEXT -- example: ['S99992928', 'S99975961'],
	passport TEXT -- example: ['FALSE', 'X98167138X'],
	prefix TEXT -- example: ['Mrs.', 'Mr.'],
	first TEXT -- example: ['Rosamaria', 'Loan'],
	last TEXT -- example: ['Bashirian', 'Pfannerstill'],
	suffix TEXT -- example: ['JD', 'PhD'],
	maiden TEXT -- example: ['Schaden', 'Schuster'],
	marital TEXT -- example: ['S', 'M'],
	race TEXT -- example: ['black', 'white'],
	ethnicity TEXT -- example: ['american', 'dominican'],
	gender TEXT -- example: ['M', 'F'],
	birthplace TEXT -- example: ['Pittsfield MA US', 'Medford MA US'],
	address TEXT -- example: ['301 Eula Radial Suite 298 Brockton MA 02305 US', '18797 Karson Burgs Suite 444 Palmer Town MA 01069 '],
	PRIMARY KEY (patient)
)


CREATE TABLE procedures (
	"DATE" DATE -- example: ['2013-10-19', '2013-02-09'],
	"PATIENT" TEXT -- example: ['96b24072-e1fe-49cd-a22a-6dfb92c3994c', '71949668-1c2e-43ae-ab0a-64654608defb'],
	"ENCOUNTER" TEXT -- example: ['6f2e3935-b203-493e-a9c0-f23e847b9798', 'da4fd626-e74e-4930-91be-7fb3da7ea098'],
	"CODE" INTEGER -- example: ['252160004', '23426006'],
	"DESCRIPTION" TEXT -- example: ['Measurement of respiratory function (procedure)', 'Standard pregnancy test'],
	"REASONCODE" INTEGER -- example: ['72892002', '10509002'],
	"REASONDESCRIPTION" TEXT -- example: ['Normal pregnancy', 'Acute bronchitis (disorder)'],
	CONSTRAINT fk_procedures_encounters FOREIGN KEY("ENCOUNTER") REFERENCES encounters ("ID"),
	CONSTRAINT fk_procedures_patients FOREIGN KEY("PATIENT") REFERENCES patients (patient)
)

Question: Calculate the average age of patients with prediabetes care plan.
Evidence: SUBTRACT(SUM(deathdate), SUM(birthdate)), COUNT(patient) where REASONDESCRIPTION = 'Prediabetes' from careplans;

SQL:
SELECT CAST(SUM(CASE WHEN T1.deathdate IS NULL THEN strftime('%Y', T2.STOP) - strftime('%Y', T1.birthdate) ELSE strftime('%Y', T1.deathdate) - strftime('%Y', T1.birthdate) END) AS REAL) / COUNT(DISTINCT T1.patient) FROM patients AS T1 INNER JOIN careplans AS T2 ON T1.patient = T2.PATIENT WHERE T2.REASONDESCRIPTION = 'Prediabetes'