select users appearing at least once per week for several weeks

Assuming you have a simple table like the following:

+---------+---------------------+
| user_id | activity_date       |
+---------+---------------------+
|   23672 | 2011-04-26 14:53:02 |
|   15021 | 2011-04-26 14:52:20 |
|   15021 | 2011-04-26 14:52:09 |
|   15021 | 2011-04-26 14:51:51 |
|   15021 | 2011-04-26 14:51:38 |
|    6241 | 2011-04-26 14:51:12 |
|     168 | 2011-04-26 14:51:12 |
...

How c开发者_JAVA技巧an you select the set of user_ids which appear at least once per week for the past 4 weeks?

select user_id, 
sum(if(activity_date between now() - interval 1 week and now(),1,0)) as this_week,
sum(if(activity_date between now() - interval 2 week and now() - interval 1 week,1,0)) as last_week,
sum(if(activity_date between now() - interval 3 week and now() - interval 2 week,1,0)) as two_week_ago,
sum(if(activity_date between now() - interval 4 week and now() - interval 3 week,1,0)) as three_week_ago
from activities
where activity_date >= now() - interval 4 week 
group by user_id
having this_week > 0 and last_week > 0 and two_week_ago > 0 and three_week_ago > 0

This uses Oracle syntax, so some cleanup might be needed for MySql, but you can use a series of sub-queries to select from the table if an activity occured in each of the 4 weeks, then sum up the totals by user, and then select only users that had activity > 0 in each week.

select user_id from 
{
  select user_id, SUM(in_week_0) sum_week_0,SUM(in_week_1) sum_week_1,SUM(in_week_2) sum_week_2,SUM(in_week_3) sum_week_3 from
  (
    select user_id, 
      activity_date, 
      CASE 
        WHEN activity_date < '01-MAY-11' AND activity_date >=  '24-APR-11' THEN 1
        ELSE 0
      END in_week_0,
      CASE 
        WHEN activity_date < '24-APR-11' AND activity_date >=  '17-APR-11' THEN 1
        ELSE 0
      END in_week_1,
      CASE 
        WHEN activity_date < '17-APR-11' AND activity_date >=  '10-APR-11' THEN 1
        ELSE 0
      END in_week_2,
      CASE 
        WHEN activity_date < '10-APR-11' AND activity_date >=  '03-APR-11' THEN 1
        ELSE 0
      END in_week_3
        from table
  )
  group by user_id
)
where sum_week_0 > 0 and sum_week_1 > 0 and sum_week_2 > 0 and sum_week_3 > 0;