
CREATE TABLE follows (
	f1 INTEGER NOT NULL, 
	f2 INTEGER NOT NULL, 
	PRIMARY KEY (f1, f2), 
	FOREIGN KEY(f2) REFERENCES user_profiles (uid), 
	FOREIGN KEY(f1) REFERENCES user_profiles (uid)
)


CREATE TABLE tweets (
	id BIGINT NOT NULL, 
	uid INTEGER NOT NULL, 
	text CHAR(140) NOT NULL, 
	createdate DATETIME DEFAULT CURRENT_TIMESTAMP, 
	PRIMARY KEY (id), 
	FOREIGN KEY(uid) REFERENCES user_profiles (uid)
)


CREATE TABLE user_profiles (
	uid INTEGER NOT NULL, 
	name VARCHAR(255) DEFAULT NULL, 
	email VARCHAR(255) DEFAULT NULL, 
	partitionid INTEGER DEFAULT NULL, 
	followers INTEGER DEFAULT NULL, 
	PRIMARY KEY (uid)
)

CREATE TABLE follows (
	f1 INTEGER NOT NULL, 
	f2 INTEGER NOT NULL, 
	PRIMARY KEY (f1, f2), 
	FOREIGN KEY(f2) REFERENCES user_profiles (uid), 
	FOREIGN KEY(f1) REFERENCES user_profiles (uid)
)

/*
2 rows from follows table:
f1	f2
1	2
2	3
*/


CREATE TABLE tweets (
	id BIGINT NOT NULL, 
	uid INTEGER NOT NULL, 
	text CHAR(140) NOT NULL, 
	createdate DATETIME DEFAULT CURRENT_TIMESTAMP, 
	PRIMARY KEY (id), 
	FOREIGN KEY(uid) REFERENCES user_profiles (uid)
)

/*
2 rows from tweets table:
id	uid	text	createdate
1	1	Hello I'm Tony Stark.	2018-03-09 20:00:14
2	3	Today I have went shopping at Laffayette.	2018-03-09 20:01:02
*/


CREATE TABLE user_profiles (
	uid INTEGER NOT NULL, 
	name VARCHAR(255) DEFAULT NULL, 
	email VARCHAR(255) DEFAULT NULL, 
	partitionid INTEGER DEFAULT NULL, 
	followers INTEGER DEFAULT NULL, 
	PRIMARY KEY (uid)
)

/*
2 rows from user_profiles table:
uid	name	email	partitionid	followers
1	Iron Man	ts@richest.com	1	6662425
2	Mary	Mary@yale.edu	2	890
*/
Find the id of users who are followed by Mary and Susan.
SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Mary" INTERSECT SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Susan"
[{'Nenhum': 'Nenhum resultado encontrado'}]