How to Get Popular Posts of the Year using SQL? (2015)

At the end of each year, as a blogger, you might be interested in knowing the list of top popular posts of the year. Luckily, this can be easily obtained by running the following SQL statements (e.g. in PHPMyAdmin)

2015

You can add short codes in WordPress to get top posts easily: Adding Two Short Code Functions to WordPress: Top Posts By Number of Comments and Top Posts by Ratings

Get the number of posts

SELECT count(1) 
FROM 
  `wp_posts` 
WHERE 
  date_format(`post_date_gmt`, "%Y") = '2015' and 
  `post_type`='post' and 
  `post_status`='publish'

This returns 279 posts published in 2015.

Top 10 posts

SELECT 
  concat("https://helloacm.com/", `post_name`) 
FROM 
  `wp_posts` 
WHERE 
  date_format(`post_date_gmt`, "%Y") = '2015' and 
  `post_type`='post' and 
  `post_status`='publish' 
ORDER BY
  `comment_count` DESC
LIMIT 10

Change limit 10 accordingly if you want more posts to be listed. The rankings are (at the time of writing):

1. The Ultimate VPS from QuickHostUK
2. List of APIs
3. Linux Figlet
4. How to Setup PHP Script in Crontab to Clean Multiple WordPress Database on Same Server?
5. Freeware to Convert MP3 by Quality
6. Uptime and Cal
7. Setup Multiple SSL HTTPS on One Server
8. 300MBps High Gain Wireless N USB Adapter
9. Set Up Email when Server Reboots
10. Javascript to COmpute Stamp Duty Tax

This is based on the comment count (sorted in non-ascending order). Of course, you can get the posts based on other statistics such as GD Rating, Facebook comment count, page views etc. But this is the most simple, and effective approach.

To view the popular posts at all times, you can see this page instead. To view the Year 2016 statistics, you can go to this page instead.

Relevant Posts of the Year

• Adding Two Short Code Functions to Wordpress: Top Posts By Number of Comments and Top Posts by Ratings
• Top Posts
• How to List the Most-Voted Posts in a Year using SQL? (2016)
• How to Get Popular Posts of the Year using SQL? (2015)

–EOF (The Ultimate Computing & Technology Blog) —