Abzeichen SQL Snippets

sql

#1

Frech kopiert von What cool badge queries have you come up with? - tips & tricks - Discourse Meta

Neko:

SELECT
DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 32 AND -- 32 is the "Der “poste süße Katzen” Thread" topic
  (:backfill OR p.id IN (:post_ids) )

Dampfer:

SELECT 
cf.user_id user_id, cf.updated_at granted_at
FROM user_custom_fields cf
WHERE cf.name like 'user_field_1' AND
    LENGTH(cf.value) > 1

3^x Posts:

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 3) AND count(*) < pow(3, 5)  /* Change the 3 and 5 (x and x+2) for the different badges */
ORDER BY c DESC