
CREATE TABLE "ACCOUNTS" (
	custid BIGINT NOT NULL, 
	name VARCHAR(64) NOT NULL, 
	PRIMARY KEY (custid)
)


CREATE TABLE "CHECKING" (
	custid BIGINT NOT NULL, 
	balance FLOAT NOT NULL, 
	PRIMARY KEY (custid), 
	FOREIGN KEY(custid) REFERENCES "ACCOUNTS" (custid)
)


CREATE TABLE "SAVINGS" (
	custid BIGINT NOT NULL, 
	balance FLOAT NOT NULL, 
	PRIMARY KEY (custid), 
	FOREIGN KEY(custid) REFERENCES "ACCOUNTS" (custid)
)

CREATE TABLE "ACCOUNTS" (
	custid BIGINT NOT NULL, 
	name VARCHAR(64) NOT NULL, 
	PRIMARY KEY (custid)
)

/*
2 rows from ACCOUNTS table:
custid	name
1	Brown
2	Wang
*/


CREATE TABLE "CHECKING" (
	custid BIGINT NOT NULL, 
	balance FLOAT NOT NULL, 
	PRIMARY KEY (custid), 
	FOREIGN KEY(custid) REFERENCES "ACCOUNTS" (custid)
)

/*
2 rows from CHECKING table:
custid	balance
1	10000.0
2	2000.0
*/


CREATE TABLE "SAVINGS" (
	custid BIGINT NOT NULL, 
	balance FLOAT NOT NULL, 
	PRIMARY KEY (custid), 
	FOREIGN KEY(custid) REFERENCES "ACCOUNTS" (custid)
)

/*
2 rows from SAVINGS table:
custid	balance
1	200000.0
2	999999999.0
*/
Find the names of accounts whose checking balance is above the average checking balance, but savings balance is below the average savings balance.
SELECT T1.name FROM accounts AS T1 JOIN checking AS T2 ON T1.custid  =  T2.custid WHERE T2.balance  >  (SELECT avg(balance) FROM checking) INTERSECT SELECT T1.name FROM accounts AS T1 JOIN savings AS T2 ON T1.custid  =  T2.custid WHERE T2.balance  <  (SELECT avg(balance) FROM savings)
[{'name': 'Brown'}, {'name': 'Granger'}, {'name': 'Weeks'}]