Useful WordPress queries for comments table

WPCommentQueries.sql

-- All live comments
SELECT COUNT(*) FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'

-- Comment count grouped by author
SELECT COUNT(*) AS comment_count, comment_author, user_id FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY user_id
ORDER BY comment_count DESC

-- Comment count grouped by IP address
SELECT COUNT(*) AS comment_count, comment_author, comment_author_IP FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY comment_author_IP
ORDER BY comment_count DESC

-- Comment count logged in users
SELECT COUNT(*) AS comment_count, comment_author FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
AND user_id != 0
GROUP BY comment_author_IP
ORDER BY comment_count DESC

-- Comment count guests
SELECT COUNT(*) AS count, comment_author FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
AND user_id = 0
GROUP BY comment_author_IP
ORDER BY count DESC

-- Comment count grouped by date
SELECT COUNT(*) AS comment_count, DATE(comment_date) FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY DATE(comment_date)
ORDER BY DATE(comment_date) ASC

-- Comment count grouped by IP address and date
SELECT COUNT(*) AS comment_count, DATE(comment_date), comment_author, comment_author_IP FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY DATE(comment_date), comment_author_IP
ORDER BY DATE(comment_date) ASC, comment_count DESC