Skip to content

What message with an attachment from a particular author in a discord guild got the most reactions?

Query Name

guild_author_most_reaction_to_attachment

SQL Query


select 
    guilds_t.guild_name,
    channels_t.channel_name,
    authors_t.author_name,
    authors_t.nickname,
    reaciton_sum,
    messages_t.msg_content,
    guilds_t.id,
    channels_t.id,
    authors_t.id,
    messages_t.id as message_id
from
(
    select 
        sum(reaction_count) as reaciton_sum,
        message_id
    from 
    (
        select 
            author_messages_t.author_guild_id,
            message_id,
            channel_id,
            msg_content,
            guild_id,
            reaction_count
        from
            (
            select
                author_guild_id,
                id as messsage_id,
                msg_content
            from
                messages_t
            where
                author_guild_id = (select author_guild_id from attachments_t limit 1 offset 10)
            ) as author_messages_t
        join reactions_t on author_messages_t.messsage_id = reactions_t.message_id
    ) as messages_with_reactions_t
    group by message_id
) as msg_with_reaction_count_t
join messages_t on msg_with_reaction_count_t.message_id = messages_t.id
join channels_t on messages_t.channel_id = channels_t.id
join authors_t on messages_t.author_guild_id = authors_t.id
join guilds_t on messages_t.guild_id = guilds_t.id
order by reaciton_sum desc;

Similar Queries