XF 1.2 SQL Query Fun - Combine Two Queries - Posts & Likes 30 Days

[ProCom]

I'd love to have these two queries (that I've scavenged together from great threads here) into one query.

Basically: Number of posts and number of likes per user for the past 30 days:

UserID, Username, #_Posts, #_Likes_Received


Here is the first query:

SELECT u.username AS username, COUNT(*) AS totalPosts
FROM xf_post AS p
LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
WHERE post_date  > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -30 DAY))
GROUP BY p.user_id
ORDER BY totalPosts
DESC;

... and the second:

select u.username, count(like_id) as like_count
from xf_liked_content l
inner join xf_user u on (u.user_id = l.content_user_id)
where like_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -30 DAY))
group by content_user_id
order by like_count desc

Both of those work individually for the data over the past 30 days, but I'd love to combine both data points on one line per user.

Thanks in advance you master queriers!!!

 

[Luke F]

select user.username as username, ifnull(pc.post_count, 0) as post_count, ifnull(lc.like_count, 0) as like_count
from xf_user user

left join (
    select count(*) as post_count, p.*
    from xf_post as p
    where post_date > unix_timestamp(date_add(now(), interval -30 day))
    group by p.user_id
) as pc on (pc.user_id = user.user_id)

left join (
    select count(like_id) as like_count, l.*
    from xf_liked_content l
    where like_date > unix_timestamp(date_add(now(), interval -30 day))
    group by content_user_id
) as lc on (lc.content_user_id = user.user_id)

where user.message_count > 0 and user.like_count > 0 and (pc.post_count > 0 or lc.like_count > 0)
order by post_count desc

Probably not very efficient, also making the assumptions you don't want 0 values and you want it ordered by post count

 

[ProCom]

Wow, looks like it's working perfectly... GREAT JOB!

I'm not too worried about how efficient the queries like this are since I only run them once in a while when pulling user updates on who to recognize.

Thanks again!