StreetComplete Statistics

# Monthly StreetComplete statistics with totals and percentages
df_monthly = 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_streetcomplete AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        COUNT(DISTINCT user_name) as sc_contributors,
        CAST(SUM(edit_count) as BIGINT) as sc_edits
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE created_by = 'StreetComplete'
    GROUP BY year, month
)
SELECT 
    mt.months,
    mt.total_contributors as "Total Contributors",
    COALESCE(msc.sc_contributors, 0) as "StreetComplete Contributors",
    mt.total_edits as "Total Edits",
    COALESCE(msc.sc_edits, 0) as "StreetComplete Edits",
    ROUND((COALESCE(msc.sc_contributors, 0) * 100.0) / mt.total_contributors, 2) as "Percent Contributors using StreetComplete",
    ROUND((COALESCE(msc.sc_edits, 0) * 100.0) / mt.total_edits, 2) as "Percent Edits from StreetComplete"
FROM monthly_total mt
LEFT JOIN monthly_streetcomplete msc ON mt.year = msc.year AND mt.month = msc.month
ORDER BY mt.year, mt.month
""").df()

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

# Yearly breakdown by StreetComplete quest types
query_yearly_quests = """
WITH user_first_year AS (
    SELECT 
        user_name,
        streetcomplete_quest,
        MIN(year) as first_year
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE streetcomplete_quest IS NOT NULL AND created_by = 'StreetComplete'
    GROUP BY user_name, streetcomplete_quest
),
quest_totals AS (
    SELECT
        streetcomplete_quest as "StreetComplete Quest",
        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 streetcomplete_quest IS NOT NULL AND created_by = 'StreetComplete'
    GROUP BY streetcomplete_quest
),
yearly_metrics AS (
    SELECT
        d.year,
        d.streetcomplete_quest as "StreetComplete Quest",
        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.streetcomplete_quest = ufy.streetcomplete_quest
    WHERE d.streetcomplete_quest IS NOT NULL  AND created_by = 'StreetComplete'
    GROUP BY d.year, d.streetcomplete_quest
)
SELECT 
    ym.year,
    ym."StreetComplete Quest",
    ym."Edits",
    ym."New Contributors",
    ym."Contributors",
    qt.total_edits_all_time as "Total Edits",
    qt.total_edits_2021_now as "Total Edits (2021 - Now)",
    qt.total_contributors_all_time as "Total Contributors",
    qt.total_contributors_2021_now as "Total Contributors (2021 - Now)"
FROM yearly_metrics ym
JOIN quest_totals qt
    ON ym."StreetComplete Quest" = qt."StreetComplete Quest"
ORDER BY year DESC, "Edits" DESC
"""

df_yearly_quests = duckdb.sql(query_yearly_quests).df()

# Get all StreetComplete quests
all_contributors = df_yearly_quests.groupby("StreetComplete Quest")["Total Contributors"].first()
all_contributors_2021_now = df_yearly_quests.groupby("StreetComplete Quest")["Total Contributors (2021 - Now)"].first()
all_edits = df_yearly_quests.groupby("StreetComplete Quest")["Total Edits"].first()
all_edits_2021_now = df_yearly_quests.groupby("StreetComplete Quest")["Total Edits (2021 - Now)"].first()

table_configs = [
    util.TableConfig(
        title="All StreetComplete Quests by Edits (All Time)",
        query_or_df=df_yearly_quests[df_yearly_quests["StreetComplete Quest"].isin(all_edits.index)],
        x_axis_col="year",
        y_axis_col="StreetComplete Quest",
        value_col="Edits",
        center_columns=["Rank", "StreetComplete Quest"],
        sum_col="Total Edits",
    ),
    util.TableConfig(
        title="All StreetComplete Quests by Edits (2021 - Now)",
        query_or_df=df_yearly_quests[
            (df_yearly_quests["StreetComplete Quest"].isin(all_edits_2021_now.index))
            & (df_yearly_quests["year"] >= 2021)
        ],
        x_axis_col="year",
        y_axis_col="StreetComplete Quest",
        value_col="Edits",
        center_columns=["Rank", "StreetComplete Quest"],
        sum_col="Total Edits (2021 - Now)",
    ),
    util.TableConfig(
        title="All StreetComplete Quests by Contributors (All Time)",
        query_or_df=df_yearly_quests[df_yearly_quests["StreetComplete Quest"].isin(all_contributors.index)],
        x_axis_col="year",
        y_axis_col="StreetComplete Quest",
        value_col="Contributors",
        center_columns=["Rank", "StreetComplete Quest"],
        sum_col="Total Contributors",
    ),
    util.TableConfig(
        title="All StreetComplete Quests by Contributors (2021 - Now)",
        query_or_df=df_yearly_quests[
            (df_yearly_quests["StreetComplete Quest"].isin(all_contributors_2021_now.index))
            & (df_yearly_quests["year"] >= 2021)
        ],
        x_axis_col="year",
        y_axis_col="StreetComplete Quest",
        value_col="Contributors",
        center_columns=["Rank", "StreetComplete Quest"],
        sum_col="Total Contributors (2021 - Now)",
    ),
]

util.show_tables(table_configs)