CREATE VIEW games AS
SELECT hashtag, retweeted_id, quoted_id,
	CASE 	WHEN hashtag ~~* '%GrandTheftAutoV%' THEN 'GrandTheftAutoV'
		WHEN hashtag ~~* '%LeagueOfLegends%' THEN 'LeagueOfLegends'
	 	WHEN hashtag ~~* '%Fortnite%' THEN 'Fortnite'
	 	WHEN hashtag ~~* '%dota2%' THEN 'dota2'
	 	WHEN hashtag ~~* '%CSGO%' THEN 'CSGO'
	 	WHEN hashtag ~~* '%ApexLegends%' THEN 'ApexLegends'
	 	WHEN hashtag ~~* '%Hearthstone%' THEN 'Hearthstone'
	 	WHEN hashtag ~~* '%overwatch%' THEN 'overwatch'
	 	ELSE NULL END AS game,
	CASE 	WHEN quoted_id IS NOT NULL THEN True
		ELSE False END AS if_quoted,
	CASE 	WHEN retweeted_id IS NOT NULL THEN True
		ELSE False END AS if_retweeted
FROM(
	SELECT *
	FROM base_tweets
	JOIN hashtags
	ON base_tweets.tweet_id = hashtags.tweet_id) AS base_hash
LEFT JOIN retweeted_tweet
	ON base_hash.retweeted_tweet_id = retweeted_tweet.retweeted_id
LEFT JOIN quoted_tweets
	ON base_hash.quoted_tweet_id = quoted_tweets.quoted_id;

SELECT *
FROM games