Filter users likes out if account created x days prior to topic creation

data & reporting
1

Hey,

So I’m trying to add another filter where if a user less than 2 days before a certain topic was created then they will be not included

This is what I’ve tried. Relatively new with SQL so not sure how to go about this. Any help?

SELECT *
FROM topic t post_actions pa INNER JOIN users u ON u.id = pa.user_id
WHERE post_id = 1 
    AND post_action_type_id = 2
    AND u.created_at < t.created_at-2
2 Likes
2

I’m not sure what you’re after with this one? Could you give us a little more detail about what you’re trying to achieve?

3

I’m trying to fix the date filter to filter out users made less than 2 days before a certain topic was created. I’ve been told either datediff or INTERVAL but havent got either to work.

And I needed help with adding topic t as another inner join but I sorted that with:
FROM post_actions pa INNER JOIN users u ON u.id = pa.user_id INNER JOIN topics t on t.id = t.id
t.id shows as a number rather than a preview is there anyway to go about that aswell?

4

For this one you just need to alias t.id AS topic_id and it should work its magic for you. :+1:

There are a few more examples of Explorer magic you can use in this topic - Ids in Data Explorer - #3 by tshenry too

When you say ‘a certain topic’ will it be the same topic each time? (ie. a static topic id)

1 Like
5

Yeah the same topic each time.

So I think I’ve got it working with this. There was over 1000 results from this so I tried adding
AND t.id = post.topic_id

This shortened the list to 16 results but there were still 4 results of the same thing. Not sure how to fix that.

SELECT
    t.id as topic_id,
    pa.post_id,
    pa.user_id,
    pa.created_at,
    u.created_at
FROM  post_actions pa INNER JOIN users u ON u.id = pa.user_id INNER JOIN topics t on t.id = t.id INNER JOIN posts p on p.topic_id = p.topic_id
WHERE post_id = 15000
    AND t.id = 7000
    AND post_action_type_id = 2
    AND u.created_at < t.created_at - INTERVAL '2 DAY'

EDIT: Changed
INNER JOIN posts p on p.topic_id = p.topic_id
to
INNER JOIN posts p on p.topic_id = t.id
which cut it down to 16 results like before.

Now I think the reason theres still 16 is because t.id = t.id. Not sure what I can change it to though

6

There are a few snags with that one, though I’m not swish enough with SQL to be able to explain them fully. :slightly_smiling_face:

I think you wouldn’t need the topic_id if you already have the post_id, as the post id is unique to that post (and topic). You also seem to be trying to join the some of the extra tables on fields that are not in the second table (eg, t.id = t.id is exactly the same field within the topics table). And if the topic you’re querying against is static then you wouldn’t need a relative INTERVAL as it will always be the same date (ie. 2 days prior to post 15000 being created), so you could set that as an explicit date.

I haven’t had chance to try my own version as yet, but I learnt most of my SQL/badge skills from searching Meta for different examples and magpie-ing bits and pieces of those. :slight_smile: A lot of them are under the #data-explorer tag, and there’s a host of examples for different things in Some common badge queries idea which may be worth a browse?

7

Yeah I know but whenever I remove the AND for the topic id the results go back up to 1000 for some reason. I’ll check out the link you sent. Hopefully I can solve some of the issues there

8

I’m still not sure I’m following what you’re after here, but if you know the post_id and the date you want to include new users from then maybe something like this?

-- [params]
-- int :post_id
-- date :date


SELECT pa.user_id,
       pa.created_at AS reltime$time
FROM post_actions pa 
JOIN users u ON u.id = pa.user_id
WHERE pa.post_id = :post_id
AND post_action_type_id = 2
AND u.created_at::date > :date
ORDER BY pa.created_at DESC

This would give a list of users who liked a certain post, and who had created their account after the date you enter (ordered by when they Liked it)

2 Likes
9

I’ve managed to get it all working never known about reltime and I’ve implemented that into it. Thanks again for the help!

One last question that’s regarding the reltime. is there a way to change the name from “time” to say “Topic created”?

1 Like
10

Absolutely. :slightly_smiling_face: If you change it to reltime$topic_created that should do the trick. :+1:

11

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.