Skip to content

What is the average half life of top x% active users?

What is the average half life of a user in a discord guild?

How do we want to visualize this?

Do we have to use percentage?

It is way easier with percentage.

Single Guild SQL Query

need percentage updated inside, see "Percentage" comment to find correct line

Note you can also update the minimum message threshold, see "min_message_length" comment

SELECT 
    guilds_t.guild_name,
    guilds_t.id,
    EXTRACT(DAY FROM average_author_half_life) as average_author_half_life,
    average_author_half_life as average_author_half_life_timestamp
from
(
    select 
        guild_id,
        avg(author_half_life) as average_author_half_life
    from
    (
        select 
            guild_message_count_t.guild_id as guild_id,
            guilds_t.guild_name,
            msg_count,
            max_msg_timestamp,
            min_msg_timestamp,
            max_msg_timestamp - min_msg_timestamp as author_half_life
        from 
        (
            select * FROM
                (
                    select 
                        guild_id,
                        author_id,
                        count(content) as msg_count,
                        max(msg_timestamp) as max_msg_timestamp,
                        min(msg_timestamp) as min_msg_timestamp
                    from messages_t
                    group by guild_id, author_id
                ) as msg_something_t
            where msg_count > 2 -- min_message_length
            and guild_id = (select id from guilds_t limit 1 offset 0)
        ) as guild_message_count_t
        join guilds_t on guild_message_count_t.guild_id = guilds_t.id
        order by author_half_life desc
        limit (
            select cast(  cast(author_count as float) / 100 * 30 as Integer) -- Percentage
                as percentage_msg_count
            from 
            (
                select guild_id, COUNT(*) as author_count FROM
                    (
                        select 
                            guild_id,
                            author_id,
                            count(content) as msg_count
                        from messages_t
                        group by guild_id, author_id
                    ) as msg_something_t
                where msg_count > 2 -- min_message_length
                and guild_id = (select id from guilds_t limit 1 offset 0)
                group by guild_id
            ) as author_count_t
        )
    )  as avg_halflife_percentage_author_t
    group by guild_id
) as average_author_half_list
join guilds_t on guilds_t.id = average_author_half_list.guild_id

SQL Query

This query will return the message count and the time between the authors first and last post

select 
    guild_message_count_t.guild_id,
    guilds_t.guild_name,
    msg_count,
    max_msg_timestamp,
    min_msg_timestamp,
    max_msg_timestamp - min_msg_timestamp as author_half_life
from 
(
    select * FROM
        (
            select 
                guild_id,
                author,
                count(content) as msg_count,
                max(real_timestamp) as max_msg_timestamp,
                min(real_timestamp) as min_msg_timestamp
            from messages_t
            group by guild_id, author 
        ) as msg_something_t
    where msg_count > 20
    and guild_id = (select id from guilds_t limit 1 offset 0)
) as guild_message_count_t
join guilds_t on guild_message_count_t.guild_id = guilds_t.id
order by author_half_life asc;

SQL Query

This query has the author count so we can calculate percentage

select 
    guild_message_count_t.guild_id,
    guilds_t.guild_name,
    msg_count,
    max_msg_timestamp,
    min_msg_timestamp,
    max_msg_timestamp - min_msg_timestamp as author_half_life,
    author_count.author_count
from 
(
    select * FROM
        (
            select 
                guild_id,
                author,
                count(content) as msg_count,
                max(real_timestamp) as max_msg_timestamp,
                min(real_timestamp) as min_msg_timestamp
            from messages_t
            group by guild_id, author 
        ) as msg_something_t
    where msg_count > 20
    and guild_id = (select id from guilds_t limit 1 offset 0)
) as guild_message_count_t
join guilds_t on guild_message_count_t.guild_id = guilds_t.id
join (
    select guild_id, COUNT(*) as author_count FROM
        (
            select 
                guild_id,
                author,
                count(content) as msg_count,
                max(real_timestamp) as max_msg_timestamp,
                min(real_timestamp) as min_msg_timestamp
            from messages_t
            group by guild_id, author 
        ) as msg_something_t
    where msg_count > 20
    and guild_id = (select id from guilds_t limit 1 offset 0)
    group by guild_id
) as author_count on author_count.guild_id = guild_message_count_t.guild_id
order by author_half_life asc;

We have a problem because we can't use Author Count to do the limit unless it is a subquery