/*
../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
