Editing Software Statistics

df = duckdb.sql("""
WITH top_software AS (
    SELECT created_by
    FROM (
        SELECT
            created_by,
            COUNT(DISTINCT user_name) as total_contributors
        FROM '../changeset_data/year=*/month=*/*.parquet'
        WHERE created_by IS NOT NULL
        GROUP BY created_by
        ORDER BY total_contributors DESC
        LIMIT 10
    )
),
user_first_appearance AS (
    SELECT 
        user_name,
        year,
        month,
        created_by,
        ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY year, month) as rn
    FROM (
        SELECT DISTINCT user_name, year, month, created_by
        FROM '../changeset_data/year=*/month=*/*.parquet'
        WHERE created_by IN (SELECT created_by FROM top_software)
    )
),
first_appearances AS (
    SELECT user_name, year, month, created_by
    FROM user_first_appearance 
    WHERE rn = 1
),
monthly_contributors AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        created_by,
        COUNT(DISTINCT user_name) as "Contributors",
        SUM(edit_count) as "Edit Count"
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE created_by IN (SELECT created_by FROM top_software)
    GROUP BY year, month, created_by
),
monthly_new_contributors AS (
    SELECT
        year,
        month,
        created_by,
        COUNT(DISTINCT user_name) as "New Contributors"
    FROM first_appearances
    GROUP BY year, month, created_by
),
base_data AS (
    SELECT
        m.year,
        m.month,
        m.months,
        m.created_by,
        m."Contributors",
        COALESCE(n."New Contributors", 0) as "New Contributors",
        m."Edit Count"
    FROM monthly_contributors m
    LEFT JOIN monthly_new_contributors n ON m.year = n.year AND m.month = n.month AND m.created_by = n.created_by
)
SELECT
    months,
    created_by,
    "Contributors",
    "New Contributors",
    "Edit Count",
    SUM("New Contributors") OVER (
        PARTITION BY created_by 
        ORDER BY year, month 
        ROWS UNBOUNDED PRECEDING
    ) as "Accumulated Contributors",
    SUM("Edit Count") OVER (
        PARTITION BY created_by 
        ORDER BY year, month 
        ROWS UNBOUNDED PRECEDING
    ) as "Accumulated Edits"
FROM base_data
ORDER BY year, month, created_by
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Monthly Contributors by Top 10 Editing Software",
            label="Contributors",
            x_col="months",
            y_col="Contributors",
            group_col="created_by",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Monthly New Contributors by Top 10 Editing Software",
            label="New Contributors",
            x_col="months",
            y_col="New Contributors",
            group_col="created_by",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Monthly Edit Count by Top 10 Editing Software",
            label="Edit Count",
            x_col="months",
            y_col="Edit Count",
            group_col="created_by",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Accumulated Contributors by Top 10 Editing Software",
            label="Accumulated Contributors",
            x_col="months",
            y_col="Accumulated Contributors",
            group_col="created_by",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Accumulated Edits by Top 10 Editing Software",
            label="Accumulated Edits",
            x_col="months",
            y_col="Accumulated Edits",
            group_col="created_by",
            query_or_df=df,
        ),
    ],
)

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
	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
	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
	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()

with open("../config/replace_rules_created_by.json") as f:
    editing_software_name_to_html_link = {
        name: f'<a href="{item["link"]}">{name}</a>' for name, item in json.load(f).items() if "link" in item
    }

df["Editing Software"] = df["Editing Software"].apply(
    lambda name: editing_software_name_to_html_link[name] if name in editing_software_name_to_html_link else name
)

top_100_edits = df.groupby("Editing Software")["Total Edits"].first().nlargest(100)
top_100_contributors = df.groupby("Editing Software")["Total Contributors"].first().nlargest(100)
top_100_edits_2021_now = df.groupby("Editing Software")["Total Edits (2021 - Now)"].first().nlargest(100)
top_100_contributors_2021_now = df.groupby("Editing Software")["Total Contributors (2021 - Now)"].first().nlargest(100)

table_configs = [
    util.TableConfig(
        title="Top 100 Contributors",
        query_or_df=df[df["Editing Software"].isin(top_100_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 Contributors 2021 - Now",
        query_or_df=df[(df["Editing Software"].isin(top_100_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.TableConfig(
        title="Top 100 Edits All Time",
        query_or_df=df[df["Editing Software"].isin(top_100_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 Edits 2021 - Now",
        query_or_df=df[(df["Editing Software"].isin(top_100_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.show_tables(table_configs)

df = duckdb.sql("""
WITH top_software AS (
	SELECT created_by
	FROM (
		SELECT
			created_by,
			COUNT(DISTINCT user_name) as total_contributors
		FROM '../changeset_data/year=*/month=*/*.parquet'
		WHERE created_by IS NOT NULL
		GROUP BY created_by
		ORDER BY total_contributors DESC
		LIMIT 10
	)
),
monthly_software_contributors AS (
	SELECT 
		CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
		created_by,
		COUNT(DISTINCT user_name) as contributors
	FROM '../changeset_data/year=*/month=*/*.parquet'
	WHERE created_by IN (SELECT created_by FROM top_software)
	GROUP BY year, month, created_by
),
monthly_total_contributors AS (
	SELECT 
		CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
		COUNT(DISTINCT user_name) as total_contributors
	FROM '../changeset_data/year=*/month=*/*.parquet'
	WHERE created_by IS NOT NULL
	GROUP BY year, month
)
SELECT 
	msc.months,
	msc.created_by,
	ROUND((msc.contributors * 100.0) / mtc.total_contributors, 2) as 'Percentage of Contributors'
FROM monthly_software_contributors msc
JOIN monthly_total_contributors mtc ON msc.months = mtc.months
ORDER BY msc.months, msc.created_by""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Monthly Percentage of Contributors by Top 10 Editing Software",
            x_col="months",
            y_col="Percentage of Contributors",
            y_unit_hover_template="%",
            group_col="created_by",
            query_or_df=df,
        ),
    ],
)

df_device_metrics = duckdb.sql("""
SELECT
    CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
    device_type,
    COUNT(DISTINCT user_name) as Contributors,
    CAST(SUM(edit_count) as BIGINT) as "Edit Count"
FROM '../changeset_data/year=*/month=*/*.parquet'
WHERE device_type IS NOT NULL
GROUP BY year, month, device_type
ORDER BY year, month, device_type
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Monthly Contributors by Device Type",
            label="Contributors",
            x_col="months",
            y_col="Contributors",
            group_col="device_type",
            query_or_df=df_device_metrics,
        ),
        util.FigureConfig(
            title="Monthly Edit Count by Device Type",
            label="Edit Count",
            x_col="months",
            y_col="Edit Count",
            group_col="device_type",
            query_or_df=df_device_metrics,
        ),
    ],
)

df = duckdb.sql("""
WITH top_software AS (
	SELECT created_by
	FROM (
		SELECT
			created_by,
			COUNT(DISTINCT user_name) as total_contributors
		FROM '../changeset_data/year=*/month=*/*.parquet'
		WHERE created_by IS NOT NULL
		GROUP BY created_by
		ORDER BY total_contributors DESC
		LIMIT 10
	)
),
user_first_edit AS (
	SELECT 
		user_name,
		ARG_MIN(created_by, changeset_id) as first_created_by,
		ARG_MIN(year * 100 + month, changeset_id) as year_month
	FROM '../changeset_data/year=*/month=*/*.parquet'
	WHERE created_by IS NOT NULL
	GROUP BY user_name
),
first_software_only AS (
	SELECT 
		user_name,
		first_created_by as created_by,
		CONCAT(CAST(year_month // 100 as VARCHAR), '-', LPAD(CAST(year_month % 100 as VARCHAR), 2, '0')) as months
	FROM user_first_edit
	WHERE first_created_by IN (SELECT created_by FROM top_software)
),
monthly_first_software_counts AS (
	SELECT 
		months,
		created_by,
		COUNT(DISTINCT user_name) as first_time_users
	FROM first_software_only
	GROUP BY months, created_by
)
SELECT 
	months,
	created_by,
	first_time_users as 'First Time Contributors'
FROM monthly_first_software_counts
ORDER BY months""").df()
util.show_figure(
    [
        util.FigureConfig(
            title="Top 10 First Editing Software Per Month",
            x_col="months",
            y_col="First Time Contributors",
            group_col="created_by",
            query_or_df=df,
        ),
    ],
)