Organised Teams Statistics

# Organised teams statistics per month
df = duckdb.sql("""
WITH monthly_total AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        COUNT(DISTINCT user_name) as total_contributors,
        CAST(SUM(edit_count) as BIGINT) as total_edits
    FROM '../changeset_data/year=*/month=*/*.parquet'
    GROUP BY year, month
),
monthly_organised_team AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        COUNT(DISTINCT user_name) as team_contributors,
        CAST(SUM(edit_count) as BIGINT) as team_edits
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE organised_team IS NOT NULL
    GROUP BY year, month
)
SELECT 
    mt.months,
    mt.total_contributors as "Total Contributors",
    COALESCE(mot.team_contributors, 0) as "Organised Team Contributors",
    mt.total_edits as "Total Edits",
    COALESCE(mot.team_edits, 0) as "Organised Team Edits",
    ROUND((COALESCE(mot.team_contributors, 0) * 100.0) / mt.total_contributors, 2) as "Percent Contributors from Organised Teams",
    ROUND((COALESCE(mot.team_edits, 0) * 100.0) / mt.total_edits, 2) as "Percent Edits from Organised Teams"
FROM monthly_total mt
LEFT JOIN monthly_organised_team mot ON mt.year = mot.year AND mt.month = mot.month
ORDER BY mt.year, mt.month
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Percentage of Edits from Organised Teams",
            label="Edits Percentage",
            x_col="months",
            y_col="Percent Edits from Organised Teams",
            y_unit_hover_template="%",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Monthly Edits from Organised Teams",
            label="Edits",
            x_col="months",
            y_col="Organised Team Edits",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Percentage of Contributors from Organised Teams",
            label="Contributors Percentage",
            x_col="months",
            y_col="Percent Contributors from Organised Teams",
            y_unit_hover_template="%",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Monthly Contributors from Organised Teams",
            label="Contributors",
            x_col="months",
            y_col="Organised Team Contributors",
            query_or_df=df,
        ),
    ]
)

# Top 10 organised teams by contributors
df_top10 = duckdb.sql("""
WITH top_organised_teams AS (
    SELECT organised_team
    FROM (
        SELECT
            organised_team,
            SUM(edit_count) as total_edits
        FROM '../changeset_data/year=*/month=*/*.parquet'
        WHERE organised_team IS NOT NULL
        GROUP BY organised_team
        ORDER BY total_edits DESC
        LIMIT 10
    )
),
user_first_appearance AS (
    SELECT
        user_name,
        year,
        month,
        organised_team,
        ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY year, month) as rn
    FROM (
        SELECT DISTINCT user_name, year, month, organised_team
        FROM '../changeset_data/year=*/month=*/*.parquet'
        WHERE organised_team IN (SELECT organised_team FROM top_organised_teams)
    )
),
first_appearances AS (
    SELECT user_name, year, month, organised_team
    FROM user_first_appearance
    WHERE rn = 1
),
monthly_contributors AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        organised_team,
        COUNT(DISTINCT user_name) as "Contributors",
        CAST(SUM(edit_count) as BIGINT) as "Edits"
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE organised_team IN (SELECT organised_team FROM top_organised_teams)
    GROUP BY year, month, organised_team
),
monthly_new_contributors AS (
    SELECT
        year,
        month,
        organised_team,
        COUNT(DISTINCT user_name) as "New Contributors"
    FROM first_appearances
    GROUP BY year, month, organised_team
)
SELECT 
    mc.months,
    mc.organised_team,
    mc."Contributors",
    COALESCE(mnc."New Contributors", 0) as "New Contributors",
    mc."Edits"
FROM monthly_contributors mc
LEFT JOIN monthly_new_contributors mnc ON mc.year = mnc.year AND mc.month = mnc.month AND mc.organised_team = mnc.organised_team
ORDER BY mc.year, mc.month, mc.organised_team
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Monthly Edits by Top 10 Organised Teams",
            label="Edits",
            x_col="months",
            y_col="Edits",
            group_col="organised_team",
            query_or_df=df_top10,
        ),
        util.FigureConfig(
            title="Monthly Contributors by Top 10 Organised Teams",
            label="Contributors",
            x_col="months",
            y_col="Contributors",
            group_col="organised_team",
            query_or_df=df_top10,
        ),
        util.FigureConfig(
            title="Monthly New Contributors by Top 10 Organised Teams",
            label="New Contributors",
            x_col="months",
            y_col="New Contributors",
            group_col="organised_team",
            query_or_df=df_top10,
        ),
    ]
)

query = """
WITH organised_team_totals AS (
    SELECT
        organised_team as "Organised Team",
        CAST(SUM(edit_count) as BIGINT) as total_edits_all_time,
        CAST(COUNT(DISTINCT user_name) as BIGINT) as total_contributors_all_time
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE organised_team IS NOT NULL
    GROUP BY organised_team
),
yearly_metrics AS (
    SELECT
        d.year,
        d.organised_team as "Organised Team",
        CAST(SUM(d.edit_count) as BIGINT) as "Edits",
        CAST(COUNT(DISTINCT d.user_name) as BIGINT) as "Contributors"
    FROM '../changeset_data/year=*/month=*/*.parquet' d
    WHERE d.organised_team IS NOT NULL
    GROUP BY d.year, d.organised_team
)
SELECT 
    ym.year,
    ym."Organised Team",
    ym."Edits", 
    ym."Contributors",
    ott.total_edits_all_time as "Total Edits",
    ott.total_contributors_all_time as "Total Contributors"
FROM yearly_metrics ym
JOIN organised_team_totals ott ON ym."Organised Team" = ott."Organised Team"
ORDER BY year DESC, "Edits" DESC
"""
df_all = duckdb.sql(query).df()

table_configs = [
    util.TableConfig(
        title="Edits Per Organised Team by Year",
        label="Edits",
        query_or_df=df_all,
        x_axis_col="year",
        y_axis_col="Organised Team",
        value_col="Edits",
        center_columns=["Rank", "Organised Team"],
        sum_col="Total Edits",
    ),
    util.TableConfig(
        title="Contributors Per Organised Team by Year",
        label="Contributors",
        query_or_df=df_all,
        x_axis_col="year",
        y_axis_col="Organised Team",
        value_col="Contributors",
        center_columns=["Rank", "Organised Team"],
        sum_col="Total Contributors",
    ),
]

util.show_tables(table_configs)