Xenforum statistic

CREATE VIEW stats_nejoblibenejsi AS SELECT u.username AS Username, COUNT( p.post_id ) AS MsgCount, SUM( p.likes ) AS LikesSum
FROM xf_user u
JOIN xf_post p ON u.user_id = p.user_id
GROUP BY u.user_id
ORDER BY LikesSum DESC
LIMIT 0 , 100;

CREATE VIEW stats_nejproduktivnejsi AS SELECT u.username AS Username, COUNT( p.post_id ) AS MsgCount
FROM xf_user u
JOIN xf_post p ON u.user_id = p.user_id
ORDER BY MsgCount DESC
LIMIT 0 , 100;


CREATE VIEW stats_oblibenost AS SELECT u.username AS Username, COUNT( p.post_id ) AS MsgCount, SUM( p.likes ) AS LikesSum, TRUNCATE( (SUM( p.likes ) / COUNT( p.post_id ) )*100, 0 ) AS Percentile
FROM xf_user u
JOIN xf_post p ON u.user_id = p.user_id
GROUP BY u.user_id
ORDER BY Percentile DESC
LIMIT 0 , 100;


CREATE VIEW stats_spam AS SELECT u.username AS Username, COUNT( p.post_id ) AS MsgCount, FROM_UNIXTIME(u.register_date) AS FromDate, TRUNCATE(COUNT( p.post_id )/ DATEDIFF(NOW(),FROM_UNIXTIME(u.register_date)),2) AS Spam
FROM xf_user u
JOIN xf_post p ON u.user_id = p.user_id
GROUP BY u.user_id
ORDER BY Spam DESC
LIMIT 0 , 100