GitHub - wojciech-graj/reddit-graph: A graph of Reddit

gunzip wiki.gz
pg_restore -d DB_NAME wiki

SELECT
    count(*)
FROM
    subreddits;

SELECT
    count(*)
FROM
    subreddits
WHERE
    nsfw IS NOT NULL;

SELECT
    count(*)
FROM
    subreddits
WHERE
    nsfw IS NOT NULL
    AND NOT disconnected;

SELECT
    count(*)
FROM
    wikis;

SELECT
    count(*),
    sum(total)
FROM
    wiki_refs;

SELECT
    count(*),
    sum(total)
FROM
    wiki_refs
    JOIN subreddits ON wiki_refs.subreddit_to = subreddits.name
WHERE
    nsfw IS NOT NULL;

SELECT
    count(*),
    sum(total)
FROM
    wiki_refs
WHERE
    subreddit_to = subreddit_from;

SELECT
    subreddit_from,
    count(*)
FROM (
    SELECT
        subreddit_from
    FROM
        wiki_refs
    GROUP BY
        subreddit_to,
        subreddit_from) AS subreddits_from
GROUP BY
    subreddit_from
ORDER BY
    count(*) DESC
LIMIT 10;

SELECT
    subreddit_from,
    count(total)
FROM
    wiki_refs
WHERE
    subreddit_from <> subreddit_to
GROUP BY
    subreddit_from
ORDER BY
    sum(TOTAL) DESC
LIMIT 10;

SELECT
    subreddit_to,
    sum(total)
FROM
    wiki_refs
WHERE
    subreddit_from <> subreddit_to
GROUP BY
    subreddit_to
ORDER BY
    sum(TOTAL) DESC
LIMIT 10;

SELECT
    subreddit_to,
    count(*)
FROM (
    SELECT
        subreddit_to
    FROM
        wiki_refs
    GROUP BY
        subreddit_to,
        subreddit_from) AS subreddits_to
GROUP BY
    subreddit_to
ORDER BY
    count(*) DESC
LIMIT 10;

SELECT
    wiki_refs.subreddit_to,
    sum(wiki_refs.total)
FROM
    wiki_refs
    JOIN subreddits ON subreddits.name = wiki_refs.subreddit_to
WHERE
    subreddits.nsfw IS NULL
GROUP BY
    wiki_refs.subreddit_to
ORDER BY
    sum(wiki_refs.total) DESC
LIMIT 10;

SELECT
    subreddit,
    count(*)
FROM
    wikis
GROUP BY
    subreddit
ORDER BY
    count(*) DESC
LIMIT 10;

SELECT
    name,
    count(*)
FROM
    wikis
GROUP BY
    name
ORDER BY
    count(*) DESC
LIMIT 10;

SELECT name AS id, subscribers AS size, subscribers, nsfw FROM subreddits WHERE nsfw IS NOT NULL AND NOT disconnected

SELECT subreddit_from AS source, subreddit_to AS target, total AS weight FROM wiki_refs JOIN subreddits ON subreddits.name = wiki_refs.subreddit_to WHERE subreddits.nsfw IS NOT NULL AND NOT subreddits.disconnected