CODEWITHSUNDEEP
mysqlsqldatecount
Carlos2W
Carlos2W

Reputation: 2594

Count occurence of values between 2 dynamic dates in MySQL

I have a table with a row for each events for a user. I want to retrieve every user who got 2 or more events in the same week (with up to 7 days between them).

Event Table :

id   -   user_id   -   date
 1   -       1     -   2015/04/02
 2   -       1     -   2015/04/05
 3   -       1     -   2015/09/12
 4   -      14     -   2016/01/21
 5   -      14     -   2016/02/18

So the expected results should be

user_id   -   count(date in same week)
 1        -         2

The user_id 14 should not be in results, because its events are not in the same week.

Does someone know how to do it entirely in MySQL ?

Upvotes: 0

Views: 528

Answers (2)

sagi
sagi

Reputation: 40491

If you just need the list of the user_id's that have more then 1 occurrences in 7 days, then you can use EXISTS() with DATEDIFF()

SELECT distinct user_id
FROM EventTable e
WHERE EXISTS(SELECT 1 FROM EventTable e2
             WHERE ABS(DATEDIFF(e.date,e2.date)) < 7
              AND e.user_id = e2.user_id)

Upvotes: 1

Barmar
Barmar

Reputation: 782105

If you just need to know if they have 2 in the same week, you can use a self-join where the condition is that the second table has an event within 7 days of the first.

SELECT distinct a.user_id
FROM events AS a
JOIN events AS b 
ON a.user_id = b.user_id 
    AND b.date BETWEEN DATE_ADD(a.date, INTERVAL 1 DAY) AND DATE_ADD(a.date, INTERVAL 7 DAY)

Upvotes: 1

Related Questions