Query: least 15 records in the last seven days

[dsat]

How to make a query, if I want to find a userID who is at least 15 records in the last seven days of the table "example" field 'cID=2'.

table "example"

pID | cID | userID | rTime
1, 2, 34, 2010-11-04 16:36:29
2, 2, 27, 2010-11-04 18:45:02
...

[Demaestro]

You want to make use of the count function and the having clause.

SELECT userID FROM table_name GROUP BY userID HAVING count(userID) > 14

Not sure if you need the condition or not but you can add in

where cID=2

If you need a condition you would add it like this.

SELECT userID FROM table_name WHERE cID = 2 GROUP BY userID HAVING count(userID) > 14

[dsat]

I am interested in records that are entered into the last week (last seven days)

[rocknbil]

Create table test (id int(11) primary key auto_increment, user int(11), post_date datetime);

insert into test(user,post_date) values(123,'2010-08-28 16:36:29');
insert into test(user,post_date) values(124,'2010-08-28 16:36:29');
insert into test(user,post_date) values(123,'2010-08-27 16:36:29');
insert into test(user,post_date) values(123,'2010-08-25 16:36:29');
insert into test(user,post_date) values(123,'2010-08-21 16:36:29');
/* remove this comment - note the following are older than 7 days */
insert into test(user,post_date) values(123,'2010-07-28 16:36:29');
insert into test(user,post_date) values(124,'2010-05-28 16:36:29');
insert into test(user,post_date) values(123,'2010-06-28 16:36:29');

select user from test where post_date >= date_sub(curdate(), interval 14 day) group by user;
-> 123
-> 124

select user, count(*) from test where post_date >= date_sub(curdate(), interval 14 day) group by user;
-> 123 4
-> 124 1

select user, count(*) from test where post_date >= date_sub(curdate(), interval 14 day) group by user having (count(*) >= 3);
-> 123 4

Adjust the bolded to 15 (better, use a configurable variable or form search input) and you got it. Just add your cid to the where.

Being datetime format, you can be more specific by using

date_sub(now(), interval 14 day)