Oliveiros, I think it's time to give some more details about my task. I will start with the begining :)
I have a "log" table which stores the dates when users send messages with a theme from their mobile phone. This table is named sent_messages and looks like this: receiver | theme | date ----------------------+------------+------------------------------- +40741775622 | CRISTI | 2007-06-04 07:44:45.406271+00 +40741775622 | CRISTI | 2007-06-04 07:45:01.788533+00 +40741775622 | CRISTI | 2007-06-04 07:45:03.764506+00 +40741775621 | LIA | 2007-06-04 07:45:26.309215+00 +40741775621 | LIA | 2007-06-04 07:45:28.314075+00 +40741775622 | CRISTI | 2007-06-03 07:44:00+00 +40741775622 | CRISTI | 2007-06-02 07:44:00+00 +40741775622 | CRISTI | 2007-06-01 07:44:00+00 +40741775622 | CRISTI | 2007-06-01 07:44:00+00 +40741775622 | CRISTI | 2007-06-01 07:44:00+00 +40741775622 | CRISTI | 2007-04-01 07:44:00+00 +40741775622 | CRISTI | 2007-05-01 07:44:00+00 +40741775621 | LIA | 2007-06-03 07:44:00+00 +40741775621 | LIA | 2007-06-03 07:44:00+00 +40741775621 | LIA | 2007-06-02 07:44:00+00 +40741775621 | LIA | 2007-06-02 07:44:00+00 +40741775621 | LIA | 2007-06-01 07:44:00+00 +40741775621 | LIA | 2007-06-01 07:44:00+00 +40741775621 | LIA | 2007-06-01 07:44:00+00 +40741775621 | LIA | 2007-06-01 07:44:00+00 I have also a themes table: uid | theme -----+-------- 6 | CRISTI 7 | LIA And the table named reminder_services it is used to filter users by theme from sent_messages table. This table looks like this: uid | theme_uid | activity_mt_amount | activity_min_days | activity_max_months -----+----------------+-----------------------------+---------------------------+---------------------------- 5 | 6 | 3 | 6 | 1 6 | 7 | 4 | 5 | 1 The filtering should select users which sent at least activity_mt_amount messages with theme_uid within activity_min_days consecutive days, in the maximum activity_max_months months in the past. Example: The first row of the table reminder_services says that it should be selected users which sent at least 3 messages with the theme_uid=6 (theme=CRISTI), within 6 consecutive days, in the maximum 1 month in the past. So, I created the following query: SELECT SUM(B.count), A.theme, A.receiver, A.dates FROM ( SELECT COUNT(*), sent_messages.theme, receiver, date_trunc('day',sent_messages.date) AS date, ARRAY(SELECT date::date + s.a FROM generate_series(0,activity_min_days) AS s(a)) AS dates FROM reminder_services, themes,sent_messages WHERE themes.uid=reminder_services.theme_uid AND sent_messages.theme=themes.theme AND date_trunc('day',sent_messages.date) > (now() - reminder_services.activity_max_months * INTERVAL' 1 month') GROUP BY sent_messages.theme, receiver, date, activity_min_days ) A INNER JOIN ( SELECT COUNT(*), sent_messages.theme, receiver, date_trunc('day',sent_messages.date) AS date, ARRAY(SELECT date::date + s.a FROM generate_series(0,activity_min_days) AS s(a)) AS dates FROM reminder_services, themes,sent_messages WHERE themes.uid=reminder_services.theme_uid AND sent_messages.theme=themes.theme AND date_trunc('day',sent_messages.date) > (now() - reminder_services.activity_max_months * INTERVAL' 1 month') GROUP BY sent_messages.theme, receiver, date, activity_min_days ) B ON A.theme=B.theme AND A.receiver=B.receiver AND B.date=ANY (A.dates) GROUP BY A.theme, A.receiver, A.dates; The result of this query is: sum | theme | receiver | dates -----+------------+----------------------+-------------------------------------------------------------------------------- 8 | CRISTI | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 5 | CRISTI | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 4 | CRISTI | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} 9 | CRISTI | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} 10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} 6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} 4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} 4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} (8 rows) The red colored values 9 and 4 are not correct, should be 3 respectively 2. Regards, Loredana