ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
promptratingmain_categoryhintsquery
2
What are the total number of tweets represented in `tweets`?1aggregatingJust use COUNT(*)SELECT COUNT(*) FROM tweets;
3
How many tweets are from either realDonaldTrump or HillaryClinton1filteringYou can use LIKE as the match on "Screen Name" is case-insensitive just to be safeSELECT COUNT(*)
FROM tweets
WHERE "Screen name" = 'realdonaldtrump'
OR "Screen name" = 'hillaryclinton';
4
Do a group count of tweets by the screen name, for Trump and Clinton tweets.
5
How many tweets are retweets?2filteringA retweet is defined as immediately starting with the capital letters RT. That means you shouldn't use `LIKE` because it does a case insensitive search. You do need a wildcard though.SELECT COUNT(*)
FROM tweets
WHERE SUBSTR(text, 1, 2) = 'RT'
6
What are the dates of the oldest and newest tweets?1
7
Select the 3 users with the least number of tweets, and the oldest and newest dates of their tweets.2
8
Find the 5 most popular hashtags2group-bySELECT hashtag, COUNT(*) AS hcount
FROM tw_hashtags
GROUP BY hashtag
ORDER BY hcount DESC
LIMIT 5;
9
Find the longest 5 hashtags with at least 10 uses5group-bySELECT * FROM (
SELECT hashtag, LENGTH(hashtag) AS hlen,
COUNT(*) AS hcount
FROM tw_hashtags
GROUP BY hashtag
)
WHERE hcount >= 10
ORDER BY hlen DESC
LIMIT 5
10
Select the top 5 users in order of most tweets per day.5aggregatingUse the julianday function to turn a datetime field into a day.SELECT
"Screen name" AS screen_name,
ROUND(
COUNT(*)
/
(julianday(MAX("Posted at")) - julianday(MIN("Posted at"))))
AS tweet_rate
FROM tweets
GROUP BY screen_name
ORDER BY tweet_rate DESC
LIMIT 5;
11
Find the top 5 users in order of followers per day since the account began.3transformationUse `Since`SELECT "Screen name",
ROUND(followers /
(julianday("2016-11-07") - julianday(Since))
)AS followers_per_day
FROM tw_users ORDER BY followers_per_day DESC LIMIT 5;
12
Find the top 5 users in order of followers1sorting`SELECT "Screen name" FROM tw_users ORDER BY followers DESC LIMIT 5;
13
List users by age of account1sortingUse `Since`SELECT * FROM tw_users ORDER BY since LIMIT 5;
14
Find all users with "Hillary" or "Democrat" in their biography who are not @HillaryClinton2filteringUse `Bio` and LIKE with wildcards as "Hillary" won't match "@hillaryclinton"SELECT * FROM tw_users
WHERE
"Screen Name" NOT LIKE "hillaryclinton"
AND
(Bio LIKE '%hillary%'
OR bio LIKE '%democrat%');
15
Get a count of all of Trump's tweets in the month of October 20162aggregatingSUBSTR("2016-10-12", 1, 7) is equal to "2016-10"

Use COUNT(*) to get a simple count		SELECT COUNT(*)
FROM tweets
WHERE "Screen name" LIKE "realdonaldtrump"
AND SUBSTR("Posted at", 1,7) = '2016-10';
16
Find the tweets tweeted by Trump on the day of 2016-10-202filteringSELECT *
FROM tweets
WHERE "Screen name" LIKE "realdonaldtrump"
AND SUBSTR("Posted at", 1, 10) = '2016-10-20';
17
Find all hashtags used by Trump in his tweets on the month of Oct. 20163joiningSELECT hashtag
FROM tw_hashtags
INNER JOIN
tweets ON
tw_hashtags.tweet_id = tweets.id
WHERE "Screen name" LIKE 'realdonaldtrump'
AND SUBSTR("Posted at", 1, 7) = "2016-10";
18
Get Trump's 10 favorite hashtags by count.4join-group-bySELECT hashtag, COUNT(*) AS hcount
FROM tw_hashtags
INNER JOIN
tweets ON
tw_hashtags.tweet_id = tweets.id
WHERE "Screen name"
LIKE 'realdonaldtrump'
GROUP BY hashtag
ORDER BY hcount DESC
LIMIT 10;
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Has Trump slowed down his rate of tweeting heading into the election?3group-byStates where `romney` is bigger than `obama` count as Romney statesSELECT
STRFTIME('%Y-%m-%d', "Posted at") AS ym,
COUNT(*) AS tcount
FROM tweets
WHERE "Screen name" = 'realdonaldtrump'
AND ym > '2016-10-25'
GROUP BY ym
ORDER BY ym ASC
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69