SQL Badge Query - Quick Responder!

data & reporting
1

Hello again,

So I have this query here that’s aimed at giving users that respond to a new question within 4 hours a badge.
What I have is:

SELECT users.id user_id, topics.id topic_id, posts.id post_id FROM users
JOIN posts ON (users.id = posts.user_id) 
JOIN topics ON (posts.topic_id = topics.
WHERE users.id NOT IN (-1, 1, 19)
AND topics.user_id != posts.user_id  
AND timestamp posts.created_at BETWEEN timestamp topics.created_at AND topic.created_at + interval
‘4 HOURS’ ORDER BY topics.id

I can’t seem to get it right, I keep getting 'ERROR: syntax error at or near “posts”.

Another thought that I have is:

SELECT users.id user_id, topics.id topic_id, posts.id post_id
FROM users 
JOIN posts ON (users.id = posts.user_id) 
JOIN topics ON (posts.topic_id = topics.id)
WHERE EXTRACT(EPOCH FROM posts.created_at) < EXTRACT(EPOCH FROM topics.created_at +   INTERVAL '4 HOURS')
AND users.id NOT IN (-1, 1, 19)
AND topics.user_id != posts.user_id  
ORDER BY topics.id

But I’m also getting the same syntax error.

For the time-being, I’m not worried about the ‘granted_at’ and ‘:backfill’ as I’m testing in our my postgresql database.

Any help would be appreciated!

2

I tested your query and it works on my end. The only discrepancy I can find, and you might kick yourself for this one, is that [quote=“ivanrlio, post:1, topic:36399”]
‘4 HOURS’
[/quote]
has different quotation mark characters than:[quote=“ivanrlio, post:1, topic:36399”]
‘4 HOURS’
[/quote]
I would presume that the latter is correct and the first one is the reason for throwing you a syntax error.

5 Likes
3

This… works. Thanks a lot!