select p.name,
       p.display_name,
       (select count(id)
        from comment c
        where c.creator_id = p.id) as comments_count
from person p
where local = true
order by comments_count desc
;

select p.name,
       p.display_name,
       (select count(id)
        from post p2
        where p2.creator_id = p.id) as posts_count
from person p
where local = true
order by posts_count desc
;

If your SQL client doesn’t support parametric queries, you have to replace the question mark with the comment ID manually.

select p.actor_id
from person p
         inner join comment_like cl on cl.person_id = p.id
where cl.comment_id = ?
  and cl.score = -1;

If your SQL client doesn’t support parametric queries, you have to replace the question mark with the post ID manually.

select p.actor_id
from person p
         inner join post_like pl on p.id = pl.person_id
where pl.post_id = ?
  and pl.score = -1;

If your SQL client doesn’t support parametric queries, you have to replace the question mark with the username in single quotes (for example 'rikudou' for mine). Note that this query fails if there are multiple users with same username but on different instances, in that case you should replace (select id from person where name = ?) with (select id from person where actor_id = ?) and instead of username for the question mark you need to use the link to their profile (for example 'https://lemmings.world/u/rikudou' for mine).

select p.ap_id, p.id, count(pl.id) as dislikes
from post p
         inner join post_like pl on pl.post_id = p.id
where pl.score = -1
  and p.creator_id = (select id from person where name = ?)
group by p.ap_id, p.id
order by dislikes desc
;

Read the instructions for Most disliked posts of a user above.

select c.ap_id, c.id, count(cl.id) as dislikes
from comment c
         inner join comment_like cl on cl.comment_id = c.id
where cl.score = -1
  and c.creator_id = (select id from person where name = ?)
group by c.ap_id, c.id
order by dislikes desc
;

Read instructions for Most disliked posts of a user.

select c.actor_id
from community c
         inner join community_block cb on c.id = cb.community_id
where cb.person_id = (select id from person where name = ?)
;

Read instructions for Most disliked posts of a user.

select p.actor_id
from person p
         inner join person_block pb on p.id = pb.target_id
where pb.person_id = (select id from person where name = ?)
;

If your SQL client doesn’t support parametric queries, you have to replace the :yourUsername with the username in single quotes (for example 'rikudou' for mine), same for :dislikerUsername. For additional instructions read instructions for Most disliked posts of a user.

select c.ap_id, c.id
from comment c
         inner join comment_like cl on cl.comment_id = c.id
         inner join person p on p.id = cl.person_id
where cl.score = -1
  and c.creator_id = (select id from person where name = :yourUsername)
  and p.name = :dislikerUsername;

If your SQL client doesn’t support parametric queries, you have to replace the question mark with the comment ID manually.

select c.ap_id,
       c.id,
       count(case cl.score when -1 then 1 end) as dislikes,
       count(case cl.score when 1 then 1 end)  as likes,
       sum(cl.score)                           as score
from comment_like cl
         inner join person p on cl.person_id = p.id
         inner join comment c on cl.comment_id = c.id
where p.local = true
  and cl.comment_id = ?
group by c.ap_id, c.id
;

If your SQL client doesn’t support parametric queries, you have to replace the question mark with the post ID manually.

select p.ap_id,
       p.id,
       count(case pl.score when -1 then 1 end) as dislikes,
       count(case pl.score when 1 then 1 end)  as likes,
       sum(pl.score)                           as score
from post_like pl
         inner join person pe on pl.person_id = pe.id
         inner join post p on pl.post_id = p.id
where pe.local = true
  and pl.post_id = ?
group by p.ap_id, p.id
;