summaryrefslogtreecommitdiff
path: root/query-links.sql
blob: e455941448916d0cefbf47b2db7e97378ddeda7d (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/*
../run-psql csv "$(awk '/^)/{exit}p;/user''s/{p=1}' query-links.sql)"
*/
WITH users AS (
    SELECT
        userid,
        tweetname as name,
        COUNT(*) as tweetcount,
        ROUND(RANDOM()) as isspam
    FROM tweet JOIN twitteruser
    USING (userid)
    GROUP BY userid,tweetname
    ORDER BY tweetCount DESC
    --100k is effectively everything...
    LIMIT 200000
    --OFFSET 1000
)
SELECT
    userid AS source,
    replyid AS target,
    COUNT(*) as value
FROM tweet s
WHERE
    replyid <> 0 AND
    userid <> replyid AND
    (userid IN (SELECT userid FROM users) AND
    replyid IN (SELECT userid FROM users))
GROUP BY source, target
ORDER BY value DESC