{"id":2503,"date":"2017-01-20T07:21:51","date_gmt":"2017-01-20T07:21:50","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=2503"},"modified":"2025-06-13T20:40:58","modified_gmt":"2025-06-14T03:40:58","slug":"analyze-donald-trump-tweets-couchbase-n1ql","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/analyze-donald-trump-tweets-couchbase-n1ql\/","title":{"rendered":"Analyze Donald Trump Tweets with Couchbase and N1QL"},"content":{"rendered":"

AWS Serverless Lambda Scheduled Events to Store Tweets in Couchbase<\/a> explained how to store tweets\u00a0in Couchbase using AWS Serverless Lambda. Now, this Lambda Function has been running for a few days and has collected\u00a0269 tweets from @realDonaldTrump<\/a>. This blog , inspired by SQL on Twitter: Analysis Made Easy Using N1QL<\/a>, will show how these tweets can be analyzed using\u00a0N1QL.<\/p>\n

N1QL<\/a> is a SQL-like\u00a0query language from Couchbase<\/a> that operates on JSON documents. N1QL and SQL Differences<\/a>\u00a0provide differences between N1QL and SQL. Let’s use N1QL to\u00a0reveal some interesting information from @realDonaldTrump<\/a>‘s tweets. Many thanks to Sitaram from N1QL team<\/a> to help hack the queries.<\/p>\n

How\u00a0Many Tweets<\/h2>\n

First query is to find out how many tweets are available in the database. The query is pretty simple: Query:<\/strong><\/p>\n

SELECT COUNT(*) tweet_count \r\nFROM twitter;<\/pre>\n
As you notice, the syntax is very similar to SQL. SELECT<\/a><\/code>, COUNT<\/code> and FROM<\/code>\u00a0clauses are what you are already familiar with from SQL syntax.\u00a0tweet_count<\/code> is an alias defined for the returned result. twitter<\/code> is the bucket where all the JSON documents are stored. Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"tweet_count\": 269\r\n  }\r\n]<\/pre>\n
The result is a JSON document as well.<\/p>\n
Tweet Sample JSON Document<\/h2>\n
In order to write queries on a JSON document, you need to know the structure of the\u00a0document. The next query will give you that. Query:<\/strong><\/p>\n
SELECT * \r\nFROM twitter \r\nLIMIT 1;<\/pre>\n
The new clause introduced here is LIMIT<\/a><\/code>. This allows to restrict the number of objects that are returned in a result set of SELECT<\/code>. Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"twitter\": {\r\n      \"accessLevel\": \"0\",\r\n      \"contributors\": [],\r\n      \"createdAt\": \"1480828438000\",\r\n      \"currentUserRetweetId\": \"-1\",\r\n      \"displayTextRangeEnd\": \"-1\",\r\n      \"displayTextRangeStart\": \"-1\",\r\n      \"favoriteCount\": \"116356\",\r\n      \"favorited\": false,\r\n      \"geoLocation\": null,\r\n      \"hashtagEntities\": [],\r\n      \"id\": \"805278955150471168\",\r\n      \"inReplyToScreenName\": null,\r\n      \"inReplyToStatusId\": \"-1\",\r\n      \"inReplyToUserId\": \"-1\",\r\n      \"lang\": \"en\",\r\n      \"mediaEntities\": [],\r\n      \"place\": null,\r\n      \"possiblySensitive\": false,\r\n      \"quotedStatus\": null,\r\n      \"quotedStatusId\": \"-1\",\r\n      \"rateLimitStatus\": null,\r\n      \"retweet\": false,\r\n      \"retweetCount\": \"28330\",\r\n      \"retweeted\": false,\r\n      \"retweetedByMe\": false,\r\n      \"retweetedStatus\": null,\r\n      \"scopes\": null,\r\n      \"source\": \"Twitter for Android<\/a>\",\r\n      \"symbolEntities\": [],\r\n      \"text\": \"Just tried watching Saturday Night Live - unwatchable! Totally biased, not funny and the Baldwin impersonation just can't get any worse. Sad\",\r\n      \"truncated\": false,\r\n      \"urlentities\": [],\r\n      \"user\": {\r\n        \"accessLevel\": \"0\",\r\n        \"biggerProfileImageURL\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2_bigger.jpg\",\r\n        \"biggerProfileImageURLHttps\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2_bigger.jpg\",\r\n        \"contributorsEnabled\": false,\r\n        \"createdAt\": \"1237383998000\",\r\n        \"defaultProfile\": false,\r\n        \"defaultProfileImage\": false,\r\n        \"description\": \"President-elect of the United States\",\r\n        \"descriptionURLEntities\": [],\r\n        \"email\": null,\r\n        \"favouritesCount\": \"46\",\r\n        \"followRequestSent\": false,\r\n        \"followersCount\": \"19294404\",\r\n        \"friendsCount\": \"42\",\r\n        \"geoEnabled\": true,\r\n        \"id\": \"25073877\",\r\n        \"lang\": \"en\",\r\n        \"listedCount\": \"52499\",\r\n        \"location\": \"New York, NY\",\r\n        \"miniProfileImageURL\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2_mini.jpg\",\r\n        \"miniProfileImageURLHttps\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2_mini.jpg\",\r\n        \"name\": \"Donald J. Trump\",\r\n        \"originalProfileImageURL\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2.jpg\",\r\n        \"originalProfileImageURLHttps\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2.jpg\",\r\n        \"profileBackgroundColor\": \"6D5C18\",\r\n        \"profileBackgroundImageURL\": \"https:\/\/pbs.twimg.com\/profile_background_images\/530021613\/trump_scotland__43_of_70_cc.jpg\",\r\n        \"profileBackgroundImageUrlHttps\": \"https:\/\/pbs.twimg.com\/profile_background_images\/530021613\/trump_scotland__43_of_70_cc.jpg\",\r\n        \"profileBackgroundTiled\": true,\r\n        \"profileBannerIPadRetinaURL\": \"https:\/\/pbs.twimg.com\/profile_banners\/25073877\/1479776952\/ipad_retina\",\r\n        \"profileBannerIPadURL\": \"https:\/\/pbs.twimg.com\/profile_banners\/25073877\/1479776952\/ipad\",\r\n        \"profileBannerMobileRetinaURL\": \"https:\/\/pbs.twimg.com\/profile_banners\/25073877\/1479776952\/mobile_retina\",\r\n        \"profileBannerMobileURL\": \"https:\/\/pbs.twimg.com\/profile_banners\/25073877\/1479776952\/mobile\",\r\n        \"profileBannerRetinaURL\": \"https:\/\/pbs.twimg.com\/profile_banners\/25073877\/1479776952\/web_retina\",\r\n        \"profileBannerURL\": \"https:\/\/pbs.twimg.com\/profile_banners\/25073877\/1479776952\/web\",\r\n        \"profileImageURL\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2_normal.jpg\",\r\n        \"profileImageURLHttps\": \"https:\/\/pbs.twimg.com\/profile_images\/1980294624\/DJT_Headshot_V2_normal.jpg\",\r\n        \"profileLinkColor\": \"0D5B73\",\r\n        \"profileSidebarBorderColor\": \"BDDCAD\",\r\n        \"profileSidebarFillColor\": \"C5CEC0\",\r\n        \"profileTextColor\": \"333333\",\r\n        \"profileUseBackgroundImage\": true,\r\n        \"protected\": false,\r\n        \"rateLimitStatus\": null,\r\n        \"screenName\": \"realDonaldTrump\",\r\n        \"showAllInlineMedia\": false,\r\n        \"status\": null,\r\n        \"statusesCount\": \"34269\",\r\n        \"timeZone\": \"Eastern Time (US & Canada)\",\r\n        \"translator\": false,\r\n        \"url\": \"https:\/\/t.co\/mZB2hymxC9\",\r\n        \"urlentity\": {\r\n          \"displayURL\": \"https:\/\/t.co\/mZB2hymxC9\",\r\n          \"end\": \"23\",\r\n          \"expandedURL\": \"https:\/\/t.co\/mZB2hymxC9\",\r\n          \"start\": \"0\",\r\n          \"text\": \"https:\/\/t.co\/mZB2hymxC9\",\r\n          \"url\": \"https:\/\/t.co\/mZB2hymxC9\"\r\n        },\r\n        \"utcOffset\": \"-18000\",\r\n        \"verified\": true,\r\n        \"withheldInCountries\": null\r\n      },\r\n      \"userMentionEntities\": [],\r\n      \"withheldInCountries\": null\r\n    }\r\n  }\r\n]<\/pre>\n
Top 5 Tweeting Days<\/h2>\n
After the basic queries are out of the way, let’s look at some interesting data now. What are the top 5 days\u00a0on which @realDonaldTrump<\/a> tweeted and the tweet count? Query:<\/strong><\/p>\n
SELECT SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) tweet_date, \r\n       COUNT(1) tweet_count\r\nFROM   twitter \r\nGROUP  BY SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) \r\nORDER  BY COUNT(1) DESC \r\nLIMIT  5;<\/pre>\n
Usual GROUP BY<\/code> and ORDER BY<\/code>\u00a0SQL clauses perform the same function. N1QL Functions<\/a>\u00a0apply a function to values. The createdAt<\/code> field is returned a number as a\u00a0String. TO_NUM<\/code> function converts the String to a number. MILLIS_TO_STR<\/a><\/code> function converts the\u00a0String to a date. Finally, SUBSTR<\/a><\/code> function extracts the relevant part of the date. Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"tweet_count\": 13,\r\n    \"tweet_date\": \"2017-01-17\"\r\n  },\r\n  {\r\n    \"tweet_count\": 12,\r\n    \"tweet_date\": \"2017-01-06\"\r\n  },\r\n  {\r\n    \"tweet_count\": 11,\r\n    \"tweet_date\": \"2016-12-04\"\r\n  },\r\n  {\r\n    \"tweet_count\": 10,\r\n    \"tweet_date\": \"2017-01-03\"\r\n  },\r\n  {\r\n    \"tweet_count\": 10,\r\n    \"tweet_date\": \"2017-01-04\"\r\n  }\r\n]<\/pre>\n
Jan 17th, 2017 is the most tweeted day. Now, this result is of course restricted to the data from the\u00a0JSON documents stored in the database. Does\u00a0anybody have a more\u00a0comprehensive database of @realDonaldTrump\u00a0tweets?<\/p>\n
Tweet Frequency<\/h2>\n
OK,\u00a0our database\u00a0shows that that maximum number of tweets in a day were 13. How do I find out how many days\u00a0@realDonaldTrump tweeted a certain number of times? Query:<\/strong><\/p>\n
SELECT a.tweet_count, count(1) days FROM (\r\nSELECT SUBSTR(millis_to_str(to_num(createdAt)), 0, 10) tweet_date, \r\n       COUNT(1) tweet_count\r\nFROM   twitter \r\nGROUP  BY SUBSTR(millis_to_str(to_num(createdAt)), 0, 10)\r\n) a\r\nGROUP BY a.tweet_count\r\nORDER BY a.tweet_count DESC;<\/pre>\n
This is easily achieved using N1QL nested queries<\/a>. Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"days\": 1,\r\n    \"tweet_count\": 13\r\n  },\r\n  {\r\n    \"days\": 1,\r\n    \"tweet_count\": 12\r\n  },\r\n  {\r\n    \"days\": 1,\r\n    \"tweet_count\": 11\r\n  },\r\n  {\r\n    \"days\": 2,\r\n    \"tweet_count\": 10\r\n  },\r\n  {\r\n    \"days\": 1,\r\n    \"tweet_count\": 9\r\n  },\r\n  {\r\n    \"days\": 7,\r\n    \"tweet_count\": 8\r\n  },\r\n  {\r\n    \"days\": 3,\r\n    \"tweet_count\": 7\r\n  },\r\n  {\r\n    \"days\": 7,\r\n    \"tweet_count\": 6\r\n  },\r\n  {\r\n    \"days\": 5,\r\n    \"tweet_count\": 5\r\n  },\r\n  {\r\n    \"days\": 5,\r\n    \"tweet_count\": 4\r\n  },\r\n  {\r\n    \"days\": 11,\r\n    \"tweet_count\": 3\r\n  },\r\n  {\r\n    \"days\": 3,\r\n    \"tweet_count\": 2\r\n  },\r\n  {\r\n    \"days\": 1,\r\n    \"tweet_count\": 1\r\n  }\r\n]<\/pre>\n
In 47 days, there is only one\u00a0day with a single tweet. A sum total of tweet_count<\/code>\u00a0shows that there is no single\u00a0day without\u00a0a tweet :)<\/p>\n
Most Common Hour In a Day To Tweet<\/h2>\n
@realDonaldTrump is known to tweet at 3am<\/a>. Let’s take a look what are the most common hours for him to tweet. Query:<\/strong><\/p>\n
SELECT SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) tweet_hour, \r\n       COUNT(1) tweet_count\r\nFROM   twitter \r\nGROUP  BY SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) \r\nORDER  BY tweet_count DESC \r\nLIMIT  5;<\/pre>\n
Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"tweet_count\": 39,\r\n    \"tweet_hour\": \"13\"\r\n  },\r\n  {\r\n    \"tweet_count\": 27,\r\n    \"tweet_hour\": \"12\"\r\n  },\r\n  {\r\n    \"tweet_count\": 26,\r\n    \"tweet_hour\": \"11\"\r\n  },\r\n  {\r\n    \"tweet_count\": 20,\r\n    \"tweet_hour\": \"14\"\r\n  },\r\n  {\r\n    \"tweet_count\": 15,\r\n    \"tweet_hour\": \"00\"\r\n  }\r\n]<\/pre>\n
Now seems like the controversial tweets come at 3am. But 39 tweets are coming at 1pm ET, likely right after lunch and while having a dessert ;)<\/p>\n
Common Day of The Week to Tweet<\/h2>\n
Let’s find out what are the most common day of the week to tweet. Query:<\/strong><\/p>\n
SELECT DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), \"day_of_week\") day_of_week, \r\n       COUNT(1) tweet_count\r\nFROM   twitter \r\nGROUP  BY DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), \"day_of_week\")\r\nORDER  BY tweet_count DESC;<\/pre>\n
DATE_PART_STR<\/code><\/a> is a new function\u00a0returns date part of the date. Further day_of_week<\/code>\u00a0attribute is used to get day of the week. Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"day_of_week\": 2,\r\n    \"tweet_count\": 49\r\n  },\r\n  {\r\n    \"day_of_week\": 3,\r\n    \"tweet_count\": 40\r\n  },\r\n  {\r\n    \"day_of_week\": 0,\r\n    \"tweet_count\": 40\r\n  },\r\n  {\r\n    \"day_of_week\": 5,\r\n    \"tweet_count\": 38\r\n  },\r\n  {\r\n    \"day_of_week\": 4,\r\n    \"tweet_count\": 36\r\n  },\r\n  {\r\n    \"day_of_week\": 6,\r\n    \"tweet_count\": 33\r\n  },\r\n  {\r\n    \"day_of_week\": 1,\r\n    \"tweet_count\": 33\r\n  }\r\n]<\/pre>\n
Seems like Tuesday is the most common day to tweet. Then comes Sunday and Wednesday at the same level. The performance tends to fizzle out closer to the weekend. <\/p>\n
#22417<\/a>\u00a0should allow to report the\u00a0weekday part in English.<\/p>\n
Top 5\u00a0Mentions in Tweets<\/h2>\n
Query:<\/strong><\/p>\n
SELECT COUNT(1) user_count, ue.screenName \r\n    FROM twitter \r\n    UNNEST userMentionEntities ue \r\n    GROUP by ue.screenName \r\n    ORDER by user_count DESC\r\n    LIMIT 5;<\/pre>\n
userMentionEntities<\/code> is a\u00a0nested array in the JSON document. UNNEST<\/a><\/code> conceptually performs a join of the nested array with its parent object. Each resulting joined object becomes an input to the query. Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"screenName\": \"realDonaldTrump\",\r\n    \"user_count\": 11\r\n  },\r\n  {\r\n    \"screenName\": \"FoxNews\",\r\n    \"user_count\": 7\r\n  },\r\n  {\r\n    \"screenName\": \"CNN\",\r\n    \"user_count\": 6\r\n  },\r\n  {\r\n    \"screenName\": \"NBCNews\",\r\n    \"user_count\": 5\r\n  },\r\n  {\r\n    \"screenName\": \"DanScavino\",\r\n    \"user_count\": 5\r\n  }\r\n]<\/pre>\n
Needless to say, he mentions his own\u00a0name the most in tweets! And his\u00a0two favorite TV stations Fox News<\/a> and CNN<\/a>.<\/p>\n
Top 5\u00a0Tweets with RTs<\/h2>\n
Lambda Function wakes up every 3 hours and fetches the latest tweets. So the database is a snapshot of tweets and associated information such as RTs and Favorites.\u00a0So\u00a0depending upon\u00a0when the tweet was archived, the RTs and Favorites may not be an accurate representation. But given this information, let’s take a look at the tweets with most RTs. Query:<\/strong><\/p>\n
SELECT retweetCount, text\r\nFROM twitter\r\nORDER BY retweetCount\r\nLIMIT 5;<\/pre>\n
Pretty straight forward query. Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"retweetCount\": \"10110\",\r\n    \"text\": \"the American people. I have no doubt that we will, together, MAKE AMERICA GREAT AGAIN!\"\r\n  },\r\n  {\r\n    \"retweetCount\": \"10140\",\r\n    \"text\": \"Thank you to all of the men and women who protect & serve our communities 24\/7\/365! n#LawEnforcementAppreciationDay\u2026 https:\/\/t.co\/aqUbDipSgv\"\r\n  },\r\n  {\r\n    \"retweetCount\": \"10370\",\r\n    \"text\": \"We had a great News Conference at Trump Tower today. A couple of FAKE NEWS organizations were there but the people truly get what's going on\"\r\n  },\r\n  {\r\n    \"retweetCount\": \"10414\",\r\n    \"text\": \"these companies are able to move between all 50 states, with no tax or tariff being charged. Please be forewarned prior to making a very ...\"\r\n  },\r\n  {\r\n    \"retweetCount\": \"10416\",\r\n    \"text\": \"Somebody hacked the DNC but why did they not have \"hacking defense\" like the RNC has and why have they not responded to the terrible......\"\r\n  }\r\n]<\/pre>\n
Original vs RTs<\/h2>\n
How many of tweets were written vs retweeted? Query:<\/strong><\/p>\n
SELECT retweet, count(1) count\r\nFROM twitter\r\nGROUP BY retweet;<\/pre>\n
Results:<\/strong><\/p>\n
[\r\n  {\r\n    \"count\": 253,\r\n    \"retweet\": false\r\n  },\r\n  {\r\n    \"count\": 15,\r\n    \"retweet\": true\r\n  }\r\n]<\/pre>\n
Most of the tweets are original with only a few RTs.<\/p>\n
Most Common Words in Tweet<\/h2>\n
Query:<\/strong><\/p>\n
SELECT COUNT(1) count, word \r\nFROM twitter \r\nUNNEST SPLIT(text) word\r\nGROUP BY word\r\nORDER BY count DESC;<\/pre>\n
This query uses SPLIT<\/a><\/code> function that Results:<\/p>\n
[\r\n  {\r\n    \"count\": 189,\r\n    \"word\": \"the\"\r\n  },\r\n  {\r\n    \"count\": 151,\r\n    \"word\": \"to\"\r\n  },\r\n  {\r\n    \"count\": 115,\r\n    \"word\": \"and\"\r\n  },\r\n\r\n  . . .\r\n\r\n  {\r\n    \"count\": 1,\r\n    \"word\": \"presented...Trump's\"\r\n  },\r\n  {\r\n    \"count\": 1,\r\n    \"word\": \"jobs.\"\r\n  },\r\n  {\r\n    \"count\": 1,\r\n    \"word\": \"Doing\"\r\n  }\r\n]<\/pre>\n
Frequency of words “media”, “fake” and “America” in tweets<\/h2>\n
Query:<\/strong><\/p>\n
SELECT COUNT(1) count, LOWER(w) word\r\nFROM twitter  \r\nUNNEST SPLIT(text) w  \r\nWHERE LOWER(w) IN [ \"media\", \"fake\", \"america\"] \r\nGROUP by LOWER(w) \r\nORDER BY count DESC;<\/pre>\n
LOWER<\/code><\/a> function is used to compare words independent of the case. Result:<\/strong><\/p>\n
[\r\n  {\r\n    \"count\": 12,\r\n    \"word\": \"media\"\r\n  },\r\n  {\r\n    \"count\": 9,\r\n    \"word\": \"fake\"\r\n  },\r\n  {\r\n    \"count\": 8,\r\n    \"word\": \"america\"\r\n  }\r\n]<\/pre>\n
Lambda function<\/a>\u00a0will continue to\u00a0store tweets in the database.<\/p>\n
Try these queries yourself?<\/h2>\n