From 0d0c26b589a0fedb4cf336683da4c0272a4391d1 Mon Sep 17 00:00:00 2001 From: Starfall Date: Sat, 31 Jul 2021 11:35:21 -0500 Subject: Haven't been keeping this updated, our bad --- queries.psql | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) create mode 100755 queries.psql (limited to 'queries.psql') diff --git a/queries.psql b/queries.psql new file mode 100755 index 0000000..f480851 --- /dev/null +++ b/queries.psql @@ -0,0 +1,18 @@ +-- query for everyone who's affected by a potential instance suspension +WITH instance AS (SELECT 'mastodon.social' AS name) +SELECT follower.username, follower.domain, followed.username, followed.domain +FROM instance, follows +JOIN accounts as follower ON follows.account_id=follower.id +JOIN accounts as followed ON follows.target_account_id=followed.id +WHERE followed.domain=instance.name +OR follower.domain=instance.name +ORDER BY follower.domain, follower.username, followed.username; + +-- pick out all the folks who voted for the neonazi number on a "favorite from 1-100" poll +-- note that on remote polls this only works to find local users +SELECT username, domain +FROM accounts +JOIN poll_votes ON accounts.id=poll_votes.account_id +JOIN polls ON poll_votes.poll_id=polls.id +WHERE status_id=101841397004250749 +AND choice=88; -- cgit