SQL Query

[rchelifan]

Hi folks,
I am not good at SQL and wondering if someone can help me to write an SQL query to look for users that have posted X number from X date. For example, I like to know who have posted 30 posts since 02/01/2009. I hope someone here can help with with this.

[Jake Bunce]

Here are some queries like this:

http://www.vbulletin.com/forum/showpost.php?p=764084&postcount=2

[rchelifan]

Thanks for the link. I'll see if I can change it to have it work with mine query.

[Lats]

See if this works for you...

SELECT 
    COUNT(postid) AS count, 
    user.username AS username
FROM 
    post
LEFT JOIN 
    user 
ON 
    (user.userid = post.userid)
WHERE
    dateline > unix_timestamp('2009-01-02')
GROUP BY 
    username
HAVING
    COUNT(postid) > 30
ORDER BY 
    count 
DESC

[rchelifan]

See if this works for you...

SELECT 
    COUNT(postid) AS count, 
    user.username AS username
FROM 
    post
LEFT JOIN 
    user 
ON 
    (user.userid = post.userid)
WHERE
    dateline > unix_timestamp('2009-01-02')
GROUP BY 
    username
HAVING
    COUNT(postid) > 30
ORDER BY 
    count 
DESC

It works perfectly. I only needed to add the table prefix. Thanks a lot!

[rchelifan]

Can someone help me refine this query again? After runing a couple more times, it seems like the query brings some people who don't have 30 or more posts whithin the month. Thanks a lot folks!

[Lats]

Are you adjusting the date reference?

Might have to add hours & minutes to the test.

[rchelifan]

Yes, but it seems like it also brings those who don't have more than 30 posts but posted two or three in the month. It seems like COUNT(postid) > 30 is not date limited.

[Lats]

Are you saying the count column in the results have numbers less than 30?