Haven't been keeping this updated, our bad

1 files changed, 18 insertions, 0 deletions

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;