0 reply
0 recast
2 reactions
2 replies
0 recast
0 reaction
1 reply
0 recast
1 reaction
1 reply
0 recast
0 reaction
2 replies
0 recast
1 reaction
1 reply
0 recast
0 reaction
1 reply
0 recast
0 reaction
Yes that's what i have been doing. Here is my query, but when i search for "degen" in last 168 hours, i only get 10 rows
WITH RelevantCasts AS (
SELECT
c.id,
c.fid,
c.text,
c.timestamp,
c.hash AS cast_hash,
COUNT(r.id) AS reaction_count
FROM casts c
LEFT JOIN reactions r ON c.hash = r.target_hash AND r.deleted_at IS NULL
WHERE
c.text ~* ('\y' || regexp_replace('{{query_str}}', '[$^.]', '\\\&', 'g') || '\y') AND
c.timestamp > NOW() - INTERVAL '{{time_hrs}} HOURS' AND
c.deleted_at IS NULL
GROUP BY c.id
)
SELECT
rc.id,
rc.fid,
f.fname AS fname,
rc.text,
rc.timestamp,
rc.cast_hash,
rc.reaction_count
FROM RelevantCasts rc
JOIN fnames f ON rc.fid = f.fid AND f.deleted_at IS NULL
ORDER BY rc.reaction_count DESC, rc.timestamp DESC
LIMIT 10; 1 reply
0 recast
0 reaction
