XF 1.5 SQL query to generate the Top Poster Member?

[rdn]

I want to manually generate the Top Poster Member within the last 30 days.
SQL guru please

Thanks!

 

[Jake Bunce]

SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts'
FROM xf_post AS p
INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
INNER JOIN xf_user AS u ON (u.user_id = p.user_id)
WHERE 1=1
AND p.message_state = 'visible'
AND t.discussion_state = 'visible'
AND p.post_date > UNIX_TIMESTAMP('2016-10-31')
GROUP BY p.user_id
ORDER BY NumPosts DESC

 

[rdn]

Many Thanks Jake!

 

[rdn]

A bit slow, but I think expected

Showing rows 0 - 24 (20717 total, Query took 0.9213 seconds.)

 

[rdn]

For the month of December

SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts'
FROM xf_post AS p
INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
INNER JOIN xf_user AS u ON (u.user_id = p.user_id)
WHERE 1=1
AND p.message_state = 'visible'
AND t.discussion_state = 'visible'
AND p.post_date > UNIX_TIMESTAMP('2016-12-01')
GROUP BY p.user_id
ORDER BY NumPosts DESC

 

[eva2000]

from ssh command can do

read -ep "enter database name: " dbname; read -ep "enter month in format i.e. december = 2016-12-01: " $month; echo "";mysql -e "SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts' FROM xf_post AS p INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id) INNER JOIN xf_user AS u ON (u.user_id = p.user_id) WHERE 1=1 AND p.message_state = 'visible' AND t.discussion_state = 'visible' AND p.post_date > UNIX_TIMESTAMP('"${month}"') GROUP BY p.user_id ORDER BY NumPosts DESC" $dbname

will prompt you for variables to fill in

enter database name: yourxf_dbname
enter month in format i.e. december = 2016-12-01: 2016-12-01

 

[rdn]

from ssh command can do

read -ep "enter database name: " dbname; read -ep "enter month in format i.e. december = 2016-12-01: " $month; echo "";mysql -e "SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts' FROM xf_post AS p INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id) INNER JOIN xf_user AS u ON (u.user_id = p.user_id) WHERE 1=1 AND p.message_state = 'visible' AND t.discussion_state = 'visible' AND p.post_date > UNIX_TIMESTAMP('"${month}"') GROUP BY p.user_id ORDER BY NumPosts DESC" $dbname

will prompt you for variables to fill in

enter database name: yourxf_dbname
enter month in format i.e. december = 2016-12-01: 2016-12-01

Thanks Eva.
Very useful for me every month

 

[eva2000]

more automated if you use this to grab previous months date format instead of entering each time

date +"%Y-%m-%d" -d 'last month'
2016-12-01

read -ep "enter database name: " dbname; month=$(date +"%Y-%m-%d" -d 'last month'); echo "";mysql -e "SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts' FROM xf_post AS p INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id) INNER JOIN xf_user AS u ON (u.user_id = p.user_id) WHERE 1=1 AND p.message_state = 'visible' AND t.discussion_state = 'visible' AND p.post_date > UNIX_TIMESTAMP('"${month}"') GROUP BY p.user_id ORDER BY NumPosts DESC" $dbname