Select users who have an action on two different days, with one of those days from last week
For a table event_stream
that looks like
*----*--------*---------*-------------*
| id | action | user_id | occurred_on |
*----*--------*---------*-------------*
and a users
that looks like this (simplified)
*----*-------*
| id | email |
*----*-------*
I want to select all users who have at least two activities in the table events
that happened on different days. Additionally, one of these actions must have occurred in the last 7 days.
Here's what I have so far:
SELECT x.`email`
FROM (
SELECT u.`email`, CAST(e.`occurred_on` AS DATE) d
FROM `users` u
JOIN `event_stream` e
ON e.`user_id` = u.`id`
GROUP BY CAST(e.`occurred_on` AS DATE), u.`email`
) x
GROUP BY x.`email`
HAVING COUNT(*) > 1
This should give me all users who have activity logged on at least two different days. Now I'm not really sure how to add other restrictions related to these activities, one of which should happen in the last 7 days.
source to share
Group by user_id and count conditionally:
select *
from users
where id in
(
select user_id
from event_stream
group by user_id
having count(distinct date(occurred_on)) > 1 -- at least on two different days
and sum(occurred_on >= current_date - interval 7 day) > 0 -- one in last seven days
);
In MySQL, true = 1, false = 0, so we can sum a boolean expression to calculate how often it matches.
If it is occurred_on
already a date, as the name suggests, and not datetime or timestamp, you can change count(distinct date(occurred_on))
to count(distinct occurred_on)
.
source to share
Add another condition to having
.
SELECT x.`email`
FROM (
SELECT u.`email`, CAST(e.`occurred_on` AS DATE) d
FROM `users` u
JOIN `event_stream` e
ON e.`user_id` = u.`id`
GROUP BY CAST(e.`occurred_on` AS DATE), u.`email`
) x
GROUP BY x.`email`
HAVING COUNT(*) > 1 AND SUM(DATEDIFF(now(),d)>=0 AND DATEDIFF(now(),d)<=7) > 0
DATEDIFF(now(),d)>=0 AND DATEDIFF(now(),d)<=7
evaluates to 1 or 0 depending on whether the condition is true or false.
source to share