Source Tag Analysis

df = duckdb.sql("""
WITH monthly_with_source AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        COUNT(DISTINCT user_name) as contributors_with_source,
        SUM(edit_count) as edits_with_source
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE source IS NOT NULL
    GROUP BY year, month
),
monthly_total AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        COUNT(DISTINCT user_name) as total_contributors,
        SUM(edit_count) as total_edits
    FROM '../changeset_data/year=*/month=*/*.parquet'
    GROUP BY year, month
)
SELECT 
    mt.months,
    COALESCE(ROUND((mws.contributors_with_source * 100.0) / mt.total_contributors, 2), 0) as 'Percentage Contributors with Source',
    COALESCE(ROUND((mws.edits_with_source * 100.0) / mt.total_edits, 2), 0) as 'Percentage Edits with Source'
FROM monthly_total mt
LEFT JOIN monthly_with_source mws ON mt.year = mws.year AND mt.month = mws.month
ORDER BY mt.year, mt.month
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Monthly Percentage of Contributors Using Source Tags",
            label="Contributors",
            x_col="months",
            y_col="Percentage Contributors with Source",
            y_unit_hover_template="%",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Monthly Percentage of Edits Using Source Tags",
            label="Edits",
            x_col="months",
            y_col="Percentage Edits with Source",
            y_unit_hover_template="%",
            query_or_df=df,
        ),
    ]
)

# Get top 10 sources by total edits
df = duckdb.sql("""
WITH source_expanded AS (
    SELECT 
        year,
        month,
        user_name,
        edit_count,
        unnest(source) as source_tag
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE source IS NOT NULL
),
top_sources AS (
    SELECT source_tag
    FROM (
        SELECT
            source_tag,
            SUM(edit_count) as total_edits
        FROM source_expanded
        GROUP BY source_tag
        ORDER BY total_edits DESC
        LIMIT 10
    )
),
monthly_source_data AS (
    SELECT 
        se.year,
        se.month,
        CONCAT(se.year, '-', LPAD(CAST(se.month as VARCHAR), 2, '0')) as months,
        se.source_tag,
        COUNT(DISTINCT se.user_name) as "Contributors",
        SUM(se.edit_count) as "Edits"
    FROM source_expanded se
    WHERE se.source_tag IN (SELECT source_tag FROM top_sources)
    GROUP BY se.year, se.month, se.source_tag
)
SELECT 
    months,
    source_tag,
    "Contributors",
    "Edits",
    SUM("Contributors") OVER (PARTITION BY source_tag ORDER BY year, month) as "Contributors Accumulated",
    SUM("Edits") OVER (PARTITION BY source_tag ORDER BY year, month) as "Edits Accumulated"
FROM monthly_source_data
ORDER BY year, month, source_tag
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Monthly Edits by Top 10 Source Tags",
            label="Edits",
            x_col="months",
            y_col="Edits",
            group_col="source_tag",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Accumulated Edits by Top 10 Source Tags",
            label="Edits Accumulated",
            x_col="months",
            y_col="Edits Accumulated",
            group_col="source_tag",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Monthly Contributors by Top 10 Source Tags",
            label="Contributors",
            x_col="months",
            y_col="Contributors",
            group_col="source_tag",
            query_or_df=df,
        ),
        util.FigureConfig(
            title="Accumulated Contributors by Top 10 Source Tags",
            label="Contributors Accumulated",
            x_col="months",
            y_col="Contributors Accumulated",
            group_col="source_tag",
            query_or_df=df,
        ),
    ]
)

import json

# Load replacement rules for clickable links
with open("../config/replace_rules_imagery_and_source.json") as f:
    source_name_to_html_link = {
        name: f'<a href="{item["link"]}">{name}</a>' for name, item in json.load(f).items() if "link" in item
    }

query = """
WITH source_expanded AS (
    SELECT 
        year,
        user_name,
        edit_count,
        unnest(source) as source_tag
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE source IS NOT NULL
),
user_first_year AS (
    SELECT 
        user_name,
        source_tag,
        MIN(year) as first_year
    FROM source_expanded
    GROUP BY user_name, source_tag
),
source_totals AS (
    SELECT
        source_tag as "Source",
        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 source_expanded
    GROUP BY source_tag
),
yearly_metrics AS (
    SELECT
        se.year,
        se.source_tag as "Source",
        CAST(SUM(se.edit_count) as BIGINT) as "Edits",
        CAST(COUNT(DISTINCT se.user_name) as BIGINT) as "Contributors",
        CAST(COUNT(DISTINCT CASE WHEN ufy.first_year = se.year THEN se.user_name END) as BIGINT) as "New Contributors"
    FROM source_expanded se
    LEFT JOIN user_first_year ufy 
        ON se.user_name = ufy.user_name AND se.source_tag = ufy.source_tag
    GROUP BY se.year, se.source_tag
)
SELECT 
    ym.year,
    ym."Source",
    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 source_totals st
    ON ym."Source" = st."Source"
ORDER BY year DESC, "Edits" DESC
"""
df = duckdb.sql(query).df()

# Apply HTML links to source names
df["Source"] = df["Source"].apply(
    lambda name: source_name_to_html_link[name] if name in source_name_to_html_link else name
)

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

table_configs = [
    util.TableConfig(
        title="Top 100 Sources by Contributors",
        query_or_df=df[df["Source"].isin(top_100_contributors.index)],
        x_axis_col="year",
        y_axis_col="Source",
        value_col="Contributors",
        center_columns=["Rank", "Source"],
        sum_col="Total Contributors",
    ),
    util.TableConfig(
        title="Top 100 Sources by Contributors 2021 - Now",
        query_or_df=df[(df["Source"].isin(top_100_contributors_2021_now.index)) & (df["year"] >= 2021)],
        x_axis_col="year",
        y_axis_col="Source",
        value_col="Contributors",
        center_columns=["Rank", "Source"],
        sum_col="Total Contributors (2021 - Now)",
    ),
    util.TableConfig(
        title="Top 100 Sources by Edits",
        query_or_df=df[df["Source"].isin(top_100_edits.index)],
        x_axis_col="year",
        y_axis_col="Source",
        value_col="Edits",
        center_columns=["Rank", "Source"],
        sum_col="Total Edits",
    ),
    util.TableConfig(
        title="Top 100 Sources by Edits 2021 - Now",
        query_or_df=df[(df["Source"].isin(top_100_edits_2021_now.index)) & (df["year"] >= 2021)],
        x_axis_col="year",
        y_axis_col="Source",
        value_col="Edits",
        center_columns=["Rank", "Source"],
        sum_col="Total Edits (2021 - Now)",
    ),
]

util.show_tables(table_configs)