Ever wanted to know what blog posts you write require the least effort and get the most comments? No? Well I did and I threw together a bit of SQL to help me identify the areas I can improve upon if I’m to become a serious challenger for the title of the Laziest Blogger Ever™:
SELECT p.post_title, ROUND((SUM(LENGTH(w.comment_content))/LENGTH(p.post_content))*100) AS roi, LENGTH(p.post_content) AS post_length, SUM(LENGTH(w.comment_content)) AS comment_length FROM blog.wp_posts p INNER JOIN blog.wp_comments w ON w.comment_post_ID = p.ID GROUP BY p.ID ORDER BY roi DESC LIMIT 10
It returns the top 10 blog posts ranked by the percentage return on a post measured by the number of characters invested in the opening post compared to the number of characters returned in the comments.
My top 3: