Skip to content
Advertisement

SQL Query to find top 5 cumulative tweets

How to find the top 5 months with the highest number of cumulative tweets and sort it according to the number of tweets of each month.

Table twitter structure is like this:

Token type  Month   count   Hash Tag Name
hashtag     200910  2   Babylove
hashtag     200911  2   babylove
hashtag     200912  90  babylove
hashtag     200812  100 mycoolwife
hashtag     200901  201 mycoolwife
hashtag     200910  1   mycoolwife
hashtag     200912  500 mycoolwife
hashtag     200905  23  abc
hashtag     200907  1000 abc

Output should be like this

 month  numtweets
200907  1000
200912  590
200901  201
200812  100
200905  23

Advertisement

Answer

Is this what you need ?

SELECT Month,SUM(count) as numtweets
FROM twitter 
GROUP BY Month
ORDER BY numtweets DESC
LIMIT 5

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement