SQL-Abfrage für Benutzer, die an einem Datum (oder Datumsbereich) gepostet haben

Gemeinschaft Daten & Berichterstattung
1

Kann mir jemand helfen herauszufinden, wie man Benutzer auswählt, die an einem bestimmten Datum oder in einem bestimmten Datumsbereich gepostet haben?

Vielen Dank im Voraus!

2
5 „Gefällt mir“
3

Awesome, thank you!!

1 „Gefällt mir“
4

Hmmm … I’m not sure that will do exactly what I’m looking for – say I want to query for users who’ve posted on January 1st, for example. Assuming the user has posted since then, the last_posted_at field won’t be helpful in that case.

5

Mmmm maybe this query:

5 „Gefällt mir“
6

Ah, that’s very helpful! I haven’t had time to tweak it, but it looks like just what I need to build the query I’m looking for! Thank you!

1 „Gefällt mir“
7

Sure, you should try, and If you need help, share your query here and we can help you. :+1:

2 „Gefällt mir“
8

So it turns out to be easier than I thought! This seems to accomplish what I wanted:

SELECT DISTINCT p.user_id AS user_id
FROM posts p
WHERE p.created_at > make_timestamp(2018, 10, 16, 0, 0, 0)
AND p.created_at < make_timestamp(2018, 10, 17, 0, 0, 0)

When I rewrote the query for use with a badge, it looked like this:

SELECT DISTINCT p.user_id AS user_id, created_at AS granted_at, NULL AS post_id
FROM posts p
WHERE p.created_at > make_timestamp(2018, 10, 15, 0, 0, 0)
AND p.created_at  < make_timestamp(2018, 10, 16, 0, 0, 0)

The “DISTINCT” keyword doesn’t work quite so well here because of the added fields, so I get dupes of the various users who’ve posted during the specified time period. I’ve unchecked the “Badge can be awarded multiple times” option, so I think that’s ok. Am I right?

1 „Gefällt mir“