Imagery Service Analysis

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

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

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

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

import json

# Load replacement rules for clickable links
with open("../config/replace_rules_imagery_and_source.json") as f:
    imagery_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 imagery_expanded AS (
    SELECT 
        year,
        user_name,
        edit_count,
        unnest(imagery_used) as imagery_service
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE imagery_used IS NOT NULL
),
user_first_year AS (
    SELECT 
        user_name,
        imagery_service,
        MIN(year) as first_year
    FROM imagery_expanded
    GROUP BY user_name, imagery_service
),
imagery_totals AS (
    SELECT
        imagery_service as "Imagery Service",
        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 imagery_expanded
    GROUP BY imagery_service
),
yearly_metrics AS (
    SELECT
        ie.year,
        ie.imagery_service as "Imagery Service",
        CAST(SUM(ie.edit_count) as BIGINT) as "Edits",
        CAST(COUNT(DISTINCT ie.user_name) as BIGINT) as "Contributors",
        CAST(COUNT(DISTINCT CASE WHEN ufy.first_year = ie.year THEN ie.user_name END) as BIGINT) as "New Contributors"
    FROM imagery_expanded ie
    LEFT JOIN user_first_year ufy 
        ON ie.user_name = ufy.user_name AND ie.imagery_service = ufy.imagery_service
    GROUP BY ie.year, ie.imagery_service
)
SELECT 
    ym.year,
    ym."Imagery Service",
    ym."Edits",
    ym."New Contributors",
    ym."Contributors",
    it.total_edits_all_time as "Total Edits",
    it.total_edits_2021_now as "Total Edits (2021 - Now)",
    it.total_contributors_all_time as "Total Contributors",
    it.total_contributors_2021_now as "Total Contributors (2021 - Now)"
FROM yearly_metrics ym
JOIN imagery_totals it
    ON ym."Imagery Service" = it."Imagery Service"
ORDER BY year DESC, "Edits" DESC
"""
df = duckdb.sql(query).df()

# Apply HTML links to imagery service names
df["Imagery Service"] = df["Imagery Service"].apply(
    lambda name: imagery_name_to_html_link[name] if name in imagery_name_to_html_link else name
)

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

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

util.show_tables(table_configs)