Skip to content

How many attachments of each file type are in each discord channel of a specific discord guild?

Query Name

guild_attachment_channel_file_type_count

SQL Query


select
    guilds_t.guild_name,
    channels_t.channel_name,
    attachment_extension_channel_count_t.file_extension,
    attachment_extension_channel_count_t.extension_count,
    guilds_t.id as guild_id,
    channels_t.id as channel_id
from
(
    select
        channel_id,
        file_extension,
        count(file_extension) as extension_count
    from
        attachments_t
    join messages_t on attachments_t.message_id = messages_t.id
    where messages_t.guild_id = (select id from guilds_t limit 1)
    group by channel_id, file_extension
) as attachment_extension_channel_count_t
join channels_t on attachment_extension_channel_count_t.channel_id = channels_t.id
join guilds_t on channels_t.guild_id = guilds_t.id
order by extension_count desc;






select
    guilds_t.guild_name,
    channels_t.channel_name,
    attachment_extension_channel_count_t.file_extension,
    attachment_extension_channel_count_t.extension_count,
    guilds_t.id as guild_id,
    channels_t.id as channel_id
from
(
    select
        channel_id,
        file_extension,
        count(file_extension) as extension_count
    from
        attachments_t
    join messages_t on attachments_t.message_id = messages_t.id
    where messages_t.guild_id = '{}'
    group by channel_id, file_extension
) as attachment_extension_channel_count_t
join channels_t on attachment_extension_channel_count_t.channel_id = channels_t.id
join guilds_t on channels_t.guild_id = guilds_t.id
order by channel_name, extension_count desc;

Similar Queries