Hashtags Analysis

df = duckdb.sql("""
WITH monthly_with_hashtags AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        COUNT(DISTINCT user_name) as contributors_with_hashtags,
        SUM(edit_count) as edits_with_hashtags
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE hashtags 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((mwh.contributors_with_hashtags * 100.0) / mt.total_contributors, 2), 0) as 'Percentage Contributors with Hashtags',
    COALESCE(ROUND((mwh.edits_with_hashtags * 100.0) / mt.total_edits, 2), 0) as 'Percentage Edits with Hashtags'
FROM monthly_total mt
LEFT JOIN monthly_with_hashtags mwh ON mt.year = mwh.year AND mt.month = mwh.month
ORDER BY mt.year, mt.month
""").df()

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

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

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

query = """
WITH hashtag_expanded AS (
    SELECT 
        year,
        user_name,
        edit_count,
        unnest(hashtags) as hashtag
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE hashtags IS NOT NULL
),
user_first_year AS (
    SELECT 
        user_name,
        hashtag,
        MIN(year) as first_year
    FROM hashtag_expanded
    GROUP BY user_name, hashtag
),
hashtag_totals AS (
    SELECT
        hashtag as "Hashtag",
        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 hashtag_expanded
    GROUP BY hashtag
),
yearly_metrics AS (
    SELECT
        he.year,
        he.hashtag as "Hashtag",
        CAST(SUM(he.edit_count) as BIGINT) as "Edits",
        CAST(COUNT(DISTINCT he.user_name) as BIGINT) as "Contributors",
        CAST(COUNT(DISTINCT CASE WHEN ufy.first_year = he.year THEN he.user_name END) as BIGINT) as "New Contributors"
    FROM hashtag_expanded he
    LEFT JOIN user_first_year ufy 
        ON he.user_name = ufy.user_name AND he.hashtag = ufy.hashtag
    GROUP BY he.year, he.hashtag
)
SELECT 
    ym.year,
    ym."Hashtag",
    ym."Edits",
    ym."New Contributors",
    ym."Contributors",
    ht.total_edits_all_time as "Total Edits",
    ht.total_edits_2021_now as "Total Edits (2021 - Now)",
    ht.total_contributors_all_time as "Total Contributors",
    ht.total_contributors_2021_now as "Total Contributors (2021 - Now)"
FROM yearly_metrics ym
JOIN hashtag_totals ht
    ON ym."Hashtag" = ht."Hashtag"
ORDER BY year DESC, "Edits" DESC
"""
df = duckdb.sql(query).df()

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

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

util.show_tables(table_configs)

# Create geographical maps for top 10 hashtags
df = duckdb.sql("""
WITH hashtag_expanded AS (
    SELECT 
        mid_pos_x,
        mid_pos_y,
        edit_count,
        unnest(hashtags) as hashtag
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE hashtags IS NOT NULL 
      AND mid_pos_x IS NOT NULL 
      AND mid_pos_y IS NOT NULL
),
top_hashtags AS (
    SELECT hashtag
    FROM (
        SELECT
            hashtag,
            SUM(edit_count) as total_edits
        FROM hashtag_expanded
        GROUP BY hashtag
        ORDER BY total_edits DESC
        LIMIT 10
    )
),
hashtag_geo_data AS (
    SELECT 
        he.hashtag,
        he.mid_pos_x as x,
        he.mid_pos_y as y,
        SUM(he.edit_count) as z
    FROM hashtag_expanded he
    WHERE he.hashtag IN (SELECT hashtag FROM top_hashtags)
    GROUP BY he.hashtag, he.mid_pos_x, he.mid_pos_y
)
SELECT * FROM hashtag_geo_data
ORDER BY hashtag, x, y
""").df()

# Create dropdown maps for each hashtag
configs = []
for hashtag in sorted(df["hashtag"].unique()):
    configs.append(
        util.FigureConfig(
            title=f"Edit Distribution - {hashtag}".replace("#", ""),
            x_col="x",
            y_col="y",
            z_col="z",
            query_or_df=df[df["hashtag"] == hashtag],
            plot_type="map",
        )
    )

util.show_figure(configs, type="dropdown")