Replying to
@neatnik@social.lol Thanks a lot! Your SQL query works well on my #GlitchSoc instance. I just changed it a little bit so it sorts by author (better for reporting):
WITH bounds AS (
SELECT ((extract(epoch FROM timestamptz '2026-10-15 00:00:00-04') * 1000)::bigint << 16) AS min_id
),
counts AS (
SELECT m.status_id, COUNT(*) AS mention_count
FROM mentions m, bounds b
WHERE m.status_id >= b.min_id
GROUP BY m.status_id
HAVING COUNT(*) >= 10
)
SELECT s.id,
s.created_at,
a.username || COALESCE('@' || a.domain, '') AS author,
c.mention_count,
COALESCE(s.url, s.uri) AS link
FROM counts c
JOIN statuses s ON s.id = c.status_id
JOIN accounts a ON a.id = s.account_id
WHERE s.deleted_at IS NULL
AND a.suspended_at IS NULL
AND (s.text ILIKE '%google%' OR s.spoiler_text ILIKE '%google%')
ORDER BY author DESC;
