/* ../run-psql csv "$(awk '/^)/{exit}p;/user''s/{p=1}' query-links.sql)" */ WITH users AS ( SELECT userid, tweetname as name, COUNT(*) as tweetcount, isspam AS isspam --ROUND(RANDOM()) as isspam FROM tweet t JOIN twitteruser u USING (userid) GROUP BY t.userid, u.userid ORDER BY tweetCount DESC --100k is effectively everything... LIMIT 200000 --OFFSET 1000 ) SELECT s.userid AS source, r.userid AS target, COUNT(*) as value FROM tweet s JOIN tweet r ON s.replytweetid = r.tweetid WHERE s.replytweetid <> 0 AND s.userid <> r.userid AND (s.userid IN (SELECT userid FROM users) AND r.userid IN (SELECT userid FROM users)) GROUP BY source, target ORDER BY value DESC