Bot Statistics

# Monthly bot statistics with totals, percentages, and accumulated values
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_bot AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        COUNT(DISTINCT user_name) as bot_contributors,
        CAST(SUM(edit_count) as BIGINT) as bot_edits
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE bot = true
    GROUP BY year, month
)
SELECT 
    mt.months,
    mt.year,
    mt.month,
    COALESCE(mb.bot_contributors, 0) as "Bot Contributors",
    COALESCE(mb.bot_edits, 0) as "Bot Edits",
    ROUND((COALESCE(mb.bot_contributors, 0) * 100.0) / mt.total_contributors, 2) as "Percent Bot Contributors",
    ROUND((COALESCE(mb.bot_edits, 0) * 100.0) / mt.total_edits, 2) as "Percent Bot Edits",
FROM monthly_total mt
LEFT JOIN monthly_bot mb ON mt.year = mb.year AND mt.month = mb.month
ORDER BY mt.year, mt.month
""").df()

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

# Yearly statistics for bot editing software
query = """
WITH user_first_year AS (
    SELECT 
        user_name,
        created_by,
        MIN(year) as first_year
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE created_by IS NOT NULL AND bot = true
    GROUP BY user_name, created_by
),
software_totals AS (
    SELECT
        created_by as "Editing Software",
        CAST(SUM(edit_count) as BIGINT) as total_edits_all_time,
        CAST(SUM(CASE WHEN year >= 2021 THEN edit_count ELSE 0 END) as BIGINT) as total_edits_2021_now,
        CAST(COUNT(DISTINCT user_name) as BIGINT) as total_contributors_all_time,
        CAST(COUNT(DISTINCT CASE WHEN year >= 2021 THEN user_name END) as BIGINT) as total_contributors_2021_now
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE created_by IS NOT NULL AND bot = true
    GROUP BY created_by
),
yearly_metrics AS (
    SELECT
        d.year,
        d.created_by as "Editing Software",
        CAST(SUM(d.edit_count) as BIGINT) as "Edits",
        CAST(COUNT(DISTINCT d.user_name) as BIGINT) as "Contributors",
        CAST(COUNT(DISTINCT CASE WHEN ufy.first_year = d.year THEN d.user_name END) as BIGINT) as "New Contributors"
    FROM '../changeset_data/year=*/month=*/*.parquet' d
    LEFT JOIN user_first_year ufy 
        ON d.user_name = ufy.user_name AND d.created_by = ufy.created_by
    WHERE d.created_by IS NOT NULL AND d.bot = true
    GROUP BY d.year, d.created_by
)
SELECT 
    ym.year,
    ym."Editing Software",
    ym."Edits",
    ym."New Contributors",
    ym."Contributors",
    st.total_edits_all_time as "Total Edits",
    st.total_edits_2021_now as "Total Edits (2021 - Now)",
    st.total_contributors_all_time as "Total Contributors",
    st.total_contributors_2021_now as "Total Contributors (2021 - Now)"
FROM yearly_metrics ym
JOIN software_totals st
    ON ym."Editing Software" = st."Editing Software"
ORDER BY year DESC, "Edits" DESC
"""

df = duckdb.sql(query).df()

# Get top editing software by total edits and contributors
top_edits = df.groupby("Editing Software")["Total Edits"].first().nlargest(100)
top_edits_2021_now = df.groupby("Editing Software")["Total Edits (2021 - Now)"].first().nlargest(100)
top_contributors = df.groupby("Editing Software")["Total Contributors"].first().nlargest(100)
top_contributors_2021_now = df.groupby("Editing Software")["Total Contributors (2021 - Now)"].first().nlargest(100)

table_configs = [
    util.TableConfig(
        title="Top 100 Bot Editing Software by Edits",
        query_or_df=df[df["Editing Software"].isin(top_edits.index)],
        x_axis_col="year",
        y_axis_col="Editing Software",
        value_col="Edits",
        center_columns=["Rank", "Editing Software"],
        sum_col="Total Edits",
    ),
    util.TableConfig(
        title="Top 100 Bot Editing Software by Edits 2021 - Now",
        query_or_df=df[(df["Editing Software"].isin(top_edits_2021_now.index)) & (df["year"] >= 2021)],
        x_axis_col="year",
        y_axis_col="Editing Software",
        value_col="Edits",
        center_columns=["Rank", "Editing Software"],
        sum_col="Total Edits (2021 - Now)",
    ),
    util.TableConfig(
        title="Top 100 Bot Editing Software by Contributors",
        query_or_df=df[df["Editing Software"].isin(top_contributors.index)],
        x_axis_col="year",
        y_axis_col="Editing Software",
        value_col="Contributors",
        center_columns=["Rank", "Editing Software"],
        sum_col="Total Contributors",
    ),
    util.TableConfig(
        title="Top 100 Bot Editing Software by Contributors 2021 - Now",
        query_or_df=df[(df["Editing Software"].isin(top_contributors_2021_now.index)) & (df["year"] >= 2021)],
        x_axis_col="year",
        y_axis_col="Editing Software",
        value_col="Contributors",
        center_columns=["Rank", "Editing Software"],
        sum_col="Total Contributors (2021 - Now)",
    ),
]

util.show_tables(table_configs)

# Geographic distribution of bot edits
df = duckdb.sql("""
SELECT
    mid_pos_x as x,
    mid_pos_y as y,
    SUM(edit_count) as z
FROM '../changeset_data/year=*/month=*/*.parquet'
WHERE mid_pos_x IS NOT NULL AND mid_pos_y IS NOT NULL AND bot = true
GROUP BY mid_pos_x, mid_pos_y
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Geographic Distribution of Bot Edits",
            x_col="x",
            y_col="y",
            z_col="z",
            query_or_df=df,
            plot_type="map",
        )
    ]
)