blob: f480851352ee9e7108cd374d2a2407121dad16f8 (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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;
|
