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
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?
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
There are a few snags with that one, though I’m not swish enough with SQL to be able to explain them fully.
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. 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?
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
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)