Skip to content

How to list the most reacted to URL's from a specific discord guild?

Query Name

guild_author_url_react

SQL Query


select
    guilds_t.guild_name,
    channels_t.channel_name,
    author_name,
    nickname,
    reaction_count,
    messages_t.msg_content,
    messages_t.msg_timestamp,
    guilds_t.id,
    channels_t.id,
    authors_t.id,
    authors_t.author_id
from
(
    select
        netloc,
        message_urls_t.message_id,
        sum(reactions_t.reaction_count) as reaction_count
    from
        message_urls_t
    join messages_t on message_urls_t.message_id = messages_t.id
    join reactions_t on message_urls_t.message_id = reactions_t.message_id
    -- where messages_t.guild_id = '{}'
    group by netloc, message_urls_t.message_id, reactions_t.reaction_count
) url_messages_t
join messages_t on url_messages_t.message_id = messages_t.id
join authors_t on messages_t.author_guild_id = authors_t.id
join guilds_t on authors_t.guild_id = guilds_t.id
join channels_t on messages_t.channel_id = channels_t.id
order by reaction_count desc;

Similar Queries