XF 1.5 SQL Query for the list of users whose last activity is within a time frame

LaxmiSathy

LaxmiSathy

Member
Hello,

Am running this query to get the list of users whose last activity is within a time frame:

SELECT username, date(FROM_UNIXTIME(register_date)), date(FROM_UNIXTIME(last_activity))
FROM `xf_user`
WHERE FROM_UNIXTIME(last_activity) BETWEEN '2017-10-16' AND '2017-10-24'
ORDER BY last_activity DESC

Additionally I need two more column data from the table xf_post that includes:
- timestamp of the last post made by the user in the above list and
-count of posts made by the user within this timeframe

Can you help me with the query. Thanks.
 
Sort by date Sort by votes
Hi,
If anyone looking for this query, here it is :

SELECT u.username, date(FROM_UNIXTIME(u.register_date)), date(FROM_UNIXTIME(u.last_activity)), date(MAX(FROM_UNIXTIME(p.post_date))), count(p.post_id)
FROM `xf_user` AS u
LEFT JOIN xf_post p ON (p.user_id = u.user_id)
WHERE FROM_UNIXTIME(u.last_activity) BETWEEN '2017-10-16' AND '2017-10-24' AND
FROM_UNIXTIME(p.post_date)BETWEEN '2017-10-16'AND '2017-10-24'
GROUP BY u.user_id
ORDER BY u.last_activity DESC
 
Upvote 0 Downvote
Hi,

In the above query, it outputs users who were last active within the time frame AND the users who posted during this time frame.

How to refine this query such that it also includes those users who were last active within the time frame BUT who did not post during this time frame.

Thanks in advance.
 
Upvote 0 Downvote
@Mike My idea for the query is to get users who were active in the last week and the number of posts they contributed ONLY in the last week including those users who were active and who did not post in the last week.
 
Upvote 0 Downvote
@Jake Bunce can you help me with the below query requirement:

Select the users who were active in the last week and the number of posts they contributed ONLY in the last week including those users who were active and who did not post in the last week.

result to return:

User_id, username, register_date, last_activity, post_date (last post date), count ( count of the posts done only in the last week)

Thanks in advance.
 
Upvote 0 Downvote
You must log in or register to reply here.
Back
Top Bottom