How to List the Most-Voted Posts in a Year using SQL?

In this post, we learn how to use SQL list the most popular posts (according to the number of comments for each posts). This posts, we will get some basic statistics for the posts in 2016 and also, we we will learn the top 10 posts sorted by the votings.

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

Total number of Posts

Based on this SQL:

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

We know that there are 245 posts published in 2016.

+----------+
| count(1) |
+----------+
|      245 |
+----------+
1 row in set (0.01 sec)

Top most-discussed posts

And base on the SQL, we have the most discussed posts for this year, i.e. 2016.

1
2
3
4
5
6
7
8
9
10
11

SELECT 
  post_name, post_title 
FROM 
  wp_posts 
WHERE 
  post_type = 'post' and 
  post_status = 'publish' and
  date_format(post_date_gmt, "%Y") = '2016' 
ORDER BY
 comment_count DESC 
LIMIT 10

SELECT 
  post_name, post_title 
FROM 
  wp_posts 
WHERE 
  post_type = 'post' and 
  post_status = 'publish' and
  date_format(post_date_gmt, "%Y") = '2016' 
ORDER BY
 comment_count DESC 
LIMIT 10

We know the top 10 most-discussed (as most popular) posts in 2016. Change the limit 10 to allow more posts shown, e.g. limit 20.

+--------------------------------------------------------------------------------+------------------------------------------------------------------------+
| post_name                                                                      | post_title                                                             |
+--------------------------------------------------------------------------------+------------------------------------------------------------------------+
| cc-coding-exercise-convert-a-number-to-hexadecimal                             | C/C++ Coding Exercise - Convert a Number to Hexadecimal?               |
| dynamic-programming-how-many-ways-to-connect-the-pipes                         | Dynamic Programming - How many ways to connect the pipes?              |
| when-xx-evaluates-to-false-in-cc                                               | When x==x Evaluates to FALSE in C/C++?                                 |
| how-to-compute-sum-of-two-integers-without-plus-and-minus-operators            | How to Compute Sum of Two Integers without Plus+ and Minus- Operators? |
| how-to-find-intersection-of-two-arrays-in-c                                    | How to Find Intersection of Two Arrays in C++?                         |
| bit-manipulation-how-to-set-all-bits-between-i-and-j-in-n-equal-to-m-cplusplus | Bit Manipulation: How to Set All Bits Between i and j in N equal to M? |
| how-to-check-valid-anagram-in-cc                                               | How to Check Valid Anagram in C/C++?                                   |
| c-coding-exercise-number-of-1-bits-revisited                                   | C++ Coding Exercise - Number of 1 Bits (Revisited)                     |
| dynamic-programming-integer-break                                              | Dynamic Programming - Integer Break                                    |
| cc-coding-exercise-find-the-duplicate-number                                   | C/C++ Coding Exercise - Find the Duplicate Number                      |
+--------------------------------------------------------------------------------+------------------------------------------------------------------------+
10 rows in set (0.01 sec)

These are (at the time of writing):

1. C/C++ Coding Exercise – Convert a Number to Hexadecimal?
2. Dynamic Programming – How many ways to connect the pipes?
3. When x==x Evaluates to FALSE in C/C++?
4. How to Compute Sum of Two Integers without Plus+ and Minus- Operators?
5. How to Find Intersection of Two Arrays in C++?
6. Bit Manipulation: How to Set All Bits Between i and j in N equal to M?
7. How to Check Valid Anagram in C/C++?
8. C++ Coding Exercise – Number of 1 Bits (Revisited)
9. Dynamic Programming – Integer Break
10. C/C++ Coding Exercise – Find the Duplicate Number

Most Voted Posts via SQL

Now, we can combine the voting statistics from GD Star Rating Plugin and obtain another interesting rankings.

SELECT 
   `p`.`ID`, 
   `p`.`post_title` as `title`, 
   `visitor_votes` + `user_votes` as `total_votes`, 
   `visitor_votes`, 
   `user_votes`  
FROM  
  `wp_gdsr_data_article` as `da` 
INNER JOIN 
  `wp_posts` as `p` ON `da`.`post_id` = `p`.`ID` 
WHERE
  `p`.post_type = 'post' and 
  `p`.post_status = 'publish' and  
  date_format(`p`.`post_date_gmt`, "%Y") = '2016'
HAVING
  `total_votes` > 0
ORDER BY
  `total_votes` desc 
LIMIT 10

This gives the following 10 most voted posts of the year:

+------+----------------------------------------------------------------------------+-------------+---------------+------------+
| ID   | title                                                                      | total_votes | visitor_votes | user_votes |
+------+----------------------------------------------------------------------------+-------------+---------------+------------+
| 4793 | When x==x Evaluates to FALSE in C/C++?                                     |       105.0 |          95.0 |       10.0 |
| 4679 | How to Find the Maximum of Two Integers without Using Comparison Operator? |        81.0 |          81.0 |        0.0 |
| 4191 | How to Delete a Node from a Binary Search Tree?                            |        68.0 |          58.0 |       10.0 |
| 4380 | C++ Coding Exercise: How to Check if a Large Integer is divisible by 11?   |        67.0 |          57.0 |       10.0 |
| 4329 | Microsoft Interview Question - Get the Area of the Triangle                |        66.0 |          66.0 |        0.0 |
| 4740 | PPAP in C++ and Javascript for Beginner                                    |        62.0 |          52.0 |       10.0 |
| 4166 | How to Invert a Binary Tree in C/C++?                                      |        48.0 |          48.0 |        0.0 |
| 4176 | How to Reverse Bits for 32-bit Unsigned Integer in C/C++?                  |        46.0 |          46.0 |        0.0 |
| 4178 | C++ Coding Exercise - How to Find First Missing Number?                    |        40.0 |          40.0 |        0.0 |
| 4227 | Dynamic Programming - Integer Break                                        |        31.0 |          31.0 |        0.0 |
+------+----------------------------------------------------------------------------+-------------+---------------+------------+
10 rows in set (0.01 sec)

1. When x==x Evaluates to FALSE in C/C++?
2. How to Find the Maximum of Two Integers without Using Comparison Operator?
3. How to Delete a Node from a Binary Search Tree?
4. C++ Coding Exercise: How to Check if a Large Integer is divisible by 11?
5. Microsoft Interview Question – Get the Area of the Triangle
6. PPAP in C++ and Javascript for Beginner
7. How to Invert a Binary Tree in C/C++?
8. How to Reverse Bits for 32-bit Unsigned Integer in C/C++?
9. C++ Coding Exercise – How to Find First Missing Number?
10. Dynamic Programming – Integer Break

Of course, you could modify the above both SQL queries so that it gives 12 posts i.e. one best for each month. You just need to group by the month like this:

1

GROUP BY date_format(`p`.post_date_gmt, "%M")

GROUP BY date_format(`p`.post_date_gmt, "%M")

And, also, you could create a PHP page that gives the most up-to-date ranking tables for the posts but what have been shown here are static i.e. the ranking table is generated as it is today.

SQL

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) —