MySQL Query to get "Top 25 Posters in last 3 months"

[russellmd]

Hi All,

I'm interested to run a query that will give me the First/Last Name, email address and # of posts for the top 25 posters in the last 90 days - can anyone give me a quick MySQL query that will get me this data that I can run whenever I want?

I am a PgSQL guy, and I never run queries against MySQL.

Any assistance would be wonderful...

Mitch

[lyndonb]

something like:

select u.username, count(p.id) from
user u,
posts p
where p.postdate > (unix_timestamp()-(3*31*24*60*60)) and p.userid = u.userid
group by p.id, u.username having count(p.id) = max(p.id)

note the (3*31*24*60*60) will give the seconds for 3 * 31 days, so the 3 months is not exact.

That may be something like, but the syntax may need a tweak...

[Eikinskjaldi]

SELECT u.*, count(*) as 'total' from user u
JOIN post p USING (userid)
WHERE to_days(now()) - to_days(from_unixtime(p.dateline)) <= 90
GROUP BY u.userid
ORDER BY total desc
limit 25

This will be a relatively server intensive query, I wouldn't run it too often.

[russellmd]

Thanks a bunch! :)