StreetComplete Statistics

Analysis of OpenStreetMap contributions using StreetComplete, a mobile mapping application.

Monthly Total and Percent of Edits and Contributors Using StreetComplete

code
# 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 Edits and Contributors for Each StreetComplete Quest

code
# 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)
Rank StreetComplete Quest 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 Total Edits
1 AddRoadSurface 0 699,689 534,690 428,070 495,755 1,268,801 1,372,576 1,295,203 1,097,473 1,004,706 803,184 9,000,147
2 AddBuildingType 0 0 216,587 277,087 629,114 1,734,569 1,569,203 1,844,920 424,202 200,073 151,152 7,046,907
3 AddPathSurface 0 0 42,597 147,386 305,052 945,990 792,385 770,873 696,578 718,132 652,943 5,071,936
4 AddBuildingLevels 0 84,190 137,679 159,553 396,895 1,191,453 925,245 666,977 367,324 255,946 167,381 4,352,643
5 AddWayLit 0 108,264 226,736 300,578 484,734 1,254,731 830,955 268,971 181,533 196,161 146,145 3,998,808
6 AddRoofShape 0 20,325 53,298 64,678 175,129 859,815 479,642 514,202 300,123 199,564 131,449 2,798,225
7 WayLitOverlay 0 0 0 0 0 0 297,388 564,698 701,792 505,429 448,832 2,518,139
8 AddLanes 0 30 4 0 86,518 360,137 325,064 462,378 620,850 523,541 116,263 2,494,785
9 AddHousenumber 0 22,274 41,388 46,692 140,891 449,951 373,106 363,683 274,675 265,884 217,150 2,195,694
10 SurfaceOverlay 0 0 0 0 0 0 2 385,762 576,655 547,250 467,089 1,976,758
11 AddTactilePavingCrosswalk 0 24,245 32,007 40,078 69,991 247,461 255,770 287,829 301,583 273,678 243,659 1,776,301
12 AddCycleway 0 33,375 69,445 41,524 103,286 420,511 407,895 94,324 128,611 82,114 292,384 1,673,469
13 BuildingsOverlay 0 0 0 0 0 0 0 0 712,219 597,548 344,375 1,654,142
14 AddSidewalk 0 42 0 33,321 75,386 470,991 355,272 95,050 108,272 87,934 361,040 1,587,308
15 SidewalkOverlay 0 0 0 0 0 0 264,840 394,832 364,794 285,274 225,095 1,534,835
16 AddAddressStreet 0 0 2 0 82,161 272,681 290,402 301,677 212,892 188,979 154,125 1,502,919
17 AddOpeningHours 51 34,486 26,123 29,430 47,722 127,157 159,243 188,509 207,243 223,399 212,073 1,255,436
18 AddRoadSmoothness 0 0 0 0 0 2,545 322,810 293,140 222,317 205,618 151,691 1,198,121
19 AddCrossingIsland 0 0 0 0 22,171 202,473 171,395 191,628 203,887 188,316 165,031 1,144,901
20 CyclewayOverlay 0 0 0 0 0 0 56,675 340,081 264,068 204,940 172,741 1,038,505
21 ThingsOverlay 0 0 0 0 0 0 0 0 270,984 379,936 371,436 1,022,356
22 AddPathSmoothness 0 0 0 0 0 1,980 212,163 192,632 161,298 149,350 116,301 833,724
23 CheckExistence 0 0 0 0 7,348 164,173 105,927 117,287 122,626 149,961 155,294 822,616
24 AddressOverlay 0 0 0 0 0 0 25,568 179,805 168,816 213,141 217,241 804,571
25 AddBusStopShelter 0 23,562 17,436 20,509 27,122 88,251 113,782 145,409 122,799 114,852 107,972 781,694
26 AddBinStatusOnBusStop 0 0 0 0 0 88,167 149,902 167,280 137,911 118,635 98,749 760,644
27 AddBenchStatusOnBusStop 0 0 0 0 18,642 102,800 123,969 146,966 121,991 108,371 90,914 713,653
28 AddBenchBackrest 0 0 4,472 10,399 22,578 98,042 99,346 96,556 120,618 132,867 126,231 711,109
29 AddMaxSpeed 0 115,619 121,007 39,269 52,261 76,950 73,380 79,061 63,297 49,262 39,072 709,178
30 AddTactilePavingBusStop 0 10,678 16,462 20,403 29,125 103,271 94,820 107,197 96,122 87,343 72,374 637,795
31 StreetParkingOverlay 0 0 0 0 0 0 118,759 183,457 145,473 105,726 79,666 633,081
32 AddBusStopLit 0 0 0 0 0 74,016 124,249 132,732 109,629 100,008 83,568 624,202
33 AddParkingAccess 0 0 18,103 46,664 83,880 111,953 74,965 75,448 72,547 69,807 64,437 617,804
34 AddParkingType 0 4,608 16,574 25,082 45,763 90,363 92,049 103,985 87,496 78,938 67,542 612,400
35 CheckShopExistence 0 0 0 0 0 0 0 101,804 138,866 152,371 154,567 547,608
36 AddForestLeafType 0 0 0 4,096 23,485 90,377 82,024 91,851 84,755 87,260 83,459 547,307
37 AddTreeLeafType 0 0 0 0 0 0 0 0 129,776 229,536 172,674 531,986
38 AddTactilePavingKerb 0 0 0 0 4,514 45,614 48,566 84,847 108,289 114,180 124,075 530,085
39 AddKerbHeight 0 0 0 0 3,746 43,365 47,869 92,773 102,176 112,513 115,817 518,259
40 AddCrossingType 0 10,158 27,320 34,726 47,463 120,196 114,368 140,526 21,942 51 5 516,755
41 AddParkingFee 0 0 11,451 27,191 48,498 85,785 70,693 71,573 70,316 67,735 58,157 511,399
42 AddPowerPolesMaterial 0 735 2,783 4,036 10,680 57,791 83,199 101,916 96,076 46,941 70,101 474,258
43 AddRoadName 34 40,585 28,931 30,785 50,941 58,076 58,299 52,414 47,906 46,846 54,365 469,182
44 AddLaneMarkings 0 0 0 0 0 0 0 0 0 55,193 410,329 465,522
45 AddHandrail 0 0 0 4,738 20,782 68,549 83,041 75,749 68,022 63,432 59,228 443,541
46 AddStepsRamp 0 0 0 0 15,388 84,545 81,850 77,591 51,264 50,206 48,997 409,841
47 AddTrafficSignalsButton 0 0 874 15,191 22,432 63,801 63,353 66,032 61,514 59,768 53,073 406,038
48 AddCrossingSignals 0 0 0 0 0 0 0 0 141,094 138,144 107,627 386,865
49 AddSidewalkSurface 0 0 0 0 3 0 102,399 103,634 70,026 60,170 45,881 382,113
50 AddTrafficSignalsSound 0 0 1,014 14,662 22,378 60,644 55,381 60,540 58,837 59,272 49,172 381,900
51 AddTrafficSignalsVibration 0 0 0 0 16,074 64,750 51,071 50,743 48,657 48,460 49,153 328,908
52 AddCrossingKerbHeight 0 0 0 0 0 0 0 51,616 112,242 89,037 64,149 317,044
53 AddPlaceName 0 304 0 15,109 30,907 38,500 38,329 42,972 46,931 47,202 49,009 309,263
54 AddStepsIncline 0 0 0 0 9,585 51,671 54,550 52,675 50,377 46,002 42,406 307,266
55 AddCrossing 0 0 0 0 0 16,551 33,099 55,423 79,808 59,591 48,336 292,808
56 AddTracktype 0 0 418 7,528 16,110 39,851 40,547 47,126 43,611 51,002 42,762 288,955
57 AddFireHydrantPosition 0 0 0 0 0 59 57,969 42,369 61,194 61,126 56,281 278,998
58 AddWheelchairAccessBusiness 0 10,024 9,096 11,471 19,141 35,588 43,086 51,274 46,530 28,385 23,790 278,385
59 AddCrossingMarkings 0 0 0 0 0 0 0 0 98,714 92,919 78,158 269,791
60 AddStepCount 0 0 0 0 7,322 44,508 44,260 43,278 41,809 40,816 39,670 261,663
61 AddTactilePavingSteps 0 0 0 0 0 0 0 76,534 69,879 55,457 47,628 249,498
62 PlacesOverlay 0 0 0 0 0 0 0 0 72,925 94,944 77,367 245,236
63 AddMaxHeight 0 0 1,868 5,488 7,681 36,673 37,162 41,078 36,097 33,358 27,028 226,433
64 AddBikeParkingCover 0 1,884 3,930 5,508 8,287 26,146 29,027 30,956 37,482 38,893 34,552 216,665
65 AddRoadWidth 0 0 0 0 0 0 46,174 55,164 67,636 31,993 4,020 204,987
66 AddPowerAttachment 0 0 0 0 0 0 0 0 7,128 120,646 74,095 201,869
67 AddBikeParkingCapacity 0 2,551 3,423 3,486 7,006 22,761 21,139 24,222 35,838 41,426 25,604 187,456
68 AddPlaygroundAccess 0 0 2,139 7,281 11,534 37,451 30,297 29,264 24,951 22,756 18,455 184,128
69 AddShoulder 0 0 0 0 0 11 182,151 1,317 173 0 0 183,652
70 AddSeating 0 0 0 0 0 0 26,879 46,024 47,285 31,983 26,592 178,763
71 AddCyclewaySegregation 0 0 1,899 7,571 8,324 24,265 25,251 26,219 26,624 25,278 22,978 168,409
72 AddPitchLit 0 0 0 0 0 25,420 28,134 26,349 25,982 28,720 30,310 164,915
73 MarkCompletedHighwayConstruction 0 0 2,055 3,857 7,541 31,096 28,497 21,204 23,835 23,265 18,826 160,176
74 AddPitchSurface 0 0 0 0 53 34,837 25,065 24,335 23,551 23,698 23,401 154,940
75 AddBikeParkingType 0 0 2,354 4,186 5,853 17,705 18,092 16,551 25,613 27,758 24,711 142,823
76 AddFireHydrantType 0 322 472 1,394 2,345 9,705 17,416 11,028 29,560 31,850 29,308 133,400
77 AddMaxWeight 0 0 0 929 6,549 20,533 22,185 23,846 19,957 18,061 16,466 128,526
78 AddProhibitedForPedestrians 0 0 0 5,960 7,073 24,434 21,020 19,558 17,848 12,754 18,059 126,706
79 AddBollardType 0 0 0 0 0 19,423 25,865 23,350 19,807 18,671 16,338 123,454
80 AddRailwayCrossingBarrier 0 0 7,177 9,169 5,402 12,862 22,777 20,605 17,278 15,465 12,539 123,274
81 AddFootwayPartSurface 0 0 0 1,830 9,430 25,872 21,511 17,680 15,247 15,153 12,388 119,111
82 AddRecyclingContainerMaterials 0 0 0 15 5,128 11,993 13,530 16,733 22,753 25,623 22,040 117,815
83 AddCyclewayPartSurface 0 0 0 1,988 10,379 25,372 19,188 16,427 14,248 14,130 11,643 113,375
84 AddPostboxCollectionTimes 0 0 2,074 3,113 5,486 15,814 9,818 11,348 15,096 15,801 14,808 93,358
85 AddFireHydrantDiameter 0 0 0 0 0 2,811 16,208 10,639 18,466 22,990 21,886 93,000
86 AddAmenityCover 0 0 0 0 0 0 0 12,724 23,664 27,727 25,930 90,045
87 AddBridgeStructure 0 0 528 1,204 1,939 11,218 15,772 14,817 13,585 12,722 13,236 85,021
88 ShopsOverlay 0 0 0 0 0 0 7,463 54,080 14,481 1 0 76,025
89 AddCyclewayWidth 0 0 0 0 0 0 10,335 14,388 13,547 17,666 18,593 74,529
90 AddOneway 0 0 2,424 2,639 3,383 773 13,447 14,254 15,842 12,401 7,097 72,260
91 AddVegetarian 0 341 2,575 3,323 4,210 6,794 10,085 11,143 10,303 8,072 7,443 64,289
92 CheckShopType 0 0 0 0 160 4,031 6,068 8,938 12,128 14,949 15,805 62,079
93 AddHairdresserCustomers 0 0 0 0 0 0 0 16,910 17,461 14,045 11,551 59,967
94 AddBoardType 0 0 0 0 962 6,336 6,709 6,855 10,400 13,002 12,744 57,008
95 AddEntrance 0 0 0 0 0 0 11,537 15,179 10,702 9,577 9,316 56,311
96 AddDrinkingWaterType 0 0 0 0 0 0 4,114 15,147 13,206 12,427 10,127 55,021
97 AddToiletsFee 0 466 459 725 1,236 7,160 9,145 8,904 8,804 8,802 7,802 53,503
98 AddAcceptsCards 0 0 0 0 0 0 12,639 13,023 10,871 8,650 8,146 53,329
99 MarkCompletedBuildingConstruction 0 0 215 867 1,435 7,318 8,926 9,641 8,312 7,348 7,582 51,644
100 AddIsAmenityIndoor 0 0 0 0 0 0 0 4,689 15,215 14,506 12,616 47,026
101 AddBusStopName 0 0 545 1,623 3,623 4,711 3,249 11,727 7,030 6,503 5,517 44,528
102 AddWheelchairAccessToilets 0 0 0 408 1,154 5,772 6,194 6,839 8,037 8,249 7,348 44,001
103 AddBarrierOpening 0 0 0 0 0 0 0 0 8,014 19,513 16,408 43,935
104 AddAirCompressor 0 0 0 0 0 0 9,702 10,913 9,411 7,286 6,612 43,924
105 AddBicycleBarrierType 0 0 0 0 0 1,663 10,970 8,309 7,687 6,653 5,342 40,624
106 AddSmoking 0 0 0 0 0 0 7,459 8,989 8,624 8,292 4,789 38,153
107 AddSport 0 2,379 1,615 1,795 2,830 4,852 3,718 4,020 3,628 3,254 3,987 32,078
108 AddBicycleBarrierInstallation 0 0 0 0 0 0 2,648 9,426 7,301 6,347 5,173 30,895
109 AddGeneralFee 0 0 0 133 511 2,487 4,183 4,152 4,492 6,331 8,186 30,475
110 AddAirConditioning 0 0 0 0 0 0 5,049 6,998 7,311 5,119 5,319 29,796
111 AddVegan 0 135 911 1,053 1,451 2,890 4,922 5,571 5,362 3,818 3,226 29,339
112 AddPicnicTableCover 0 0 0 0 0 4,681 18,479 5,535 41 0 0 28,736
113 AddMemorialType 0 0 0 0 0 0 5,594 7,170 5,821 4,974 4,472 28,031
114 AddWheelchairAccessPublicTransport 0 615 711 832 965 3,968 4,096 4,034 3,740 3,778 3,883 26,622
115 AddFireHydrantRef 0 0 0 0 0 0 1,128 6,197 6,773 7,154 4,999 26,251
116 AddRecyclingType 0 4,149 4,789 2,772 1,878 2,921 2,467 2,071 1,942 1,453 1,073 25,515
117 AddAtmCashIn 0 0 0 0 0 0 2,831 7,472 5,493 4,948 4,453 25,197
118 AddEntranceReference 0 0 0 0 0 0 7,599 7,672 4,355 2,458 1,977 24,061
119 AddBoardName 0 0 0 0 0 0 0 0 2,602 11,463 9,893 23,958
120 CheckOpeningHoursSigned 0 0 0 0 0 0 2,946 5,156 6,268 3,274 5,285 22,929
121 AddToiletAvailability 0 93 315 505 575 2,200 2,953 4,480 4,082 3,713 3,686 22,602
122 AddBabyChangingTable 0 229 211 277 361 1,583 2,530 3,202 2,999 4,348 3,834 19,574
123 AddChargingStationCapacity 0 0 0 0 308 2,152 2,269 2,654 4,044 4,556 3,227 19,210
124 AddChargingStationBicycles 0 0 0 0 0 0 0 0 0 2,848 14,514 17,362
125 AddBikeRentalType 0 0 0 0 0 0 6,004 4,478 2,602 2,105 1,873 17,062
126 DetailRoadSurface 0 0 0 0 16,633 93 0 3 0 0 0 16,729
127 AddCarWashType 0 38 207 244 433 2,389 3,276 3,084 2,562 2,398 2,051 16,682
128 AddBusStopRef 0 0 0 0 442 1,923 2,231 2,621 3,592 3,247 2,347 16,403
129 AddDrinkingWater 0 0 0 0 0 2,234 2,156 2,231 2,454 3,337 3,342 15,754
130 AddHalal 0 0 0 0 0 3,240 4,307 2,353 2,573 1,690 1,301 15,464
131 DetermineRecyclingGlass 0 0 0 36 1,357 2,982 2,878 2,189 1,888 1,629 1,788 14,747
132 AddReligionToWaysideShrine 0 0 110 117 608 2,114 1,896 2,776 2,096 2,425 2,107 14,249
133 AddLevel 0 0 0 0 0 537 2,047 2,678 2,956 2,806 2,324 13,348
134 AddInformationToTourism 0 0 0 0 438 2,218 2,381 2,266 2,156 1,878 1,632 12,969
135 AddChargingStationOperator 0 0 0 0 157 1,090 1,327 1,717 2,710 3,253 2,589 12,843
136 AddInternetAccess 0 3 221 309 503 1,155 1,684 2,733 2,582 1,272 2,263 12,725
137 AddMotorcycleParkingCover 0 0 5 302 611 2,409 2,629 1,927 1,975 1,800 1,057 12,715
138 AddKosher 0 0 0 0 4 4,120 3,842 1,658 1,330 868 640 12,462
139 SpecifyShopType 0 0 0 0 0 1,520 2,440 2,227 2,081 1,927 2,189 12,384
140 AddBarrierType 0 0 0 0 0 731 2,246 2,520 2,252 1,962 1,671 11,382
141 AddAtmOperator 0 0 0 0 264 1,631 1,605 1,436 1,805 1,803 1,485 10,029
142 AddPostboxRef 0 0 0 4 624 1,819 1,588 1,285 1,211 1,478 1,405 9,414
143 AddDefibrillatorLocation 0 0 0 0 0 0 0 336 2,950 3,278 2,841 9,405
144 AddIsBuildingUnderground 0 0 0 493 716 1,125 1,082 1,376 1,310 1,392 1,523 9,017
145 AddLevelThing 0 0 0 0 0 0 0 0 0 1,852 6,952 8,804
146 AddClothingBinOperator 0 0 0 0 294 1,370 1,123 988 1,460 1,784 1,206 8,225
147 AddReligionToPlaceOfWorship 0 0 292 399 718 907 1,192 1,170 1,079 942 1,192 7,891
148 AddMotorcycleParkingCapacity 0 0 6 218 330 913 1,059 1,135 1,366 1,696 980 7,703
149 AddSelfServiceLaundry 0 0 0 78 400 1,102 1,299 1,258 1,270 1,069 952 7,428
150 AddPostboxRoyalCypher 0 0 0 0 0 2,310 1,532 1,093 945 827 555 7,262
151 AddCampDrinkingWater 0 0 0 0 0 0 294 1,449 1,976 1,734 1,779 7,232
152 AddBikeParkingAccess 0 0 0 0 0 1,183 1,147 1,084 1,150 1,410 1,051 7,025
153 AddOrchardProduce 0 72 236 380 878 1,835 1,728 586 598 321 304 6,938
154 AddCampShower 0 0 0 0 0 0 266 1,177 1,272 1,404 2,640 6,759
155 AddBicyclePump 0 0 0 0 0 0 1,719 1,687 1,664 881 750 6,701
156 AddMaxPhysicalHeight 0 0 0 0 0 0 801 984 1,205 1,814 1,858 6,662
157 AddCameraType 0 2 0 0 0 726 1,432 1,097 1,263 1,137 971 6,628
158 AddSuspectedOneway 0 0 0 0 476 1,731 1,706 1,392 854 0 0 6,159
159 AddCampPower 0 0 0 0 0 0 204 1,102 1,477 1,575 1,375 5,733
160 AddBarrierOnPath 0 0 0 0 0 0 1,445 1,230 1,084 1,057 912 5,728
161 AddWheelchairAccessOutside 0 0 4 116 187 821 728 766 704 943 1,352 5,621
162 AddBikeParkingFee 0 0 0 0 0 979 850 810 803 1,000 764 5,206
163 AddArtworkType 0 0 0 0 0 0 0 0 0 0 5,028 5,028
164 AddBicycleRepairStationServices 0 0 0 0 0 0 0 0 181 2,815 1,904 4,900
165 AddStileType 0 0 0 0 0 621 847 1,033 786 851 675 4,813
166 AddIsDefibrillatorIndoor 0 0 0 0 209 1,484 1,708 1,391 6 0 0 4,798
167 AddBarrierOnRoad 0 0 0 0 0 0 972 1,016 1,052 753 811 4,604
168 AddBicycleIncline 0 0 0 0 0 0 294 1,239 1,178 946 704 4,361
169 AddAcceptsCash 0 0 0 0 142 157 190 1,341 1,007 855 591 4,283
170 AddCampType 0 0 0 0 0 0 314 1,366 1,311 1,164 116 4,271
171 AddWheelchairAccessToiletsPart 0 0 0 16 71 288 625 1,081 976 538 412 4,007
172 AddBbqFuel 0 0 0 0 0 0 0 178 1,317 1,172 1,209 3,876
173 AddGritBinSeasonal 0 0 0 0 0 0 0 1,009 1,035 683 606 3,333
174 AddBeachSurface 0 0 0 0 0 0 0 0 0 0 3,299 3,299
175 AddSanitaryDumpStation 0 0 0 0 0 0 0 1 1,214 1,048 1,009 3,272
176 AddGeneralAccess 0 0 0 0 0 0 0 0 0 0 3,130 3,130
177 AddMotorcycleParkingFee 0 0 0 0 0 0 0 0 0 277 2,667 2,944
178 AddStepCountStile 0 0 0 0 0 0 415 695 634 629 549 2,922
179 AddGlutenFree 0 0 0 0 0 0 0 0 0 1,068 1,714 2,782
180 AddBikeRentalCapacity 0 0 0 0 0 0 486 636 726 461 415 2,724
181 AddFuelSelfService 0 0 0 0 0 0 781 577 1,004 134 129 2,625
182 AddCabins 0 0 0 0 0 0 0 0 0 0 2,608 2,608
183 AddTrafficCalmingType 0 0 0 0 0 140 596 537 468 448 399 2,588
184 AddFerryAccessMotorVehicle 0 0 0 74 80 414 473 315 244 358 298 2,256
185 AddParcelLockerPickup 0 0 0 0 0 0 0 0 2,115 17 5 2,137
186 AddParcelLockerBrand 0 0 0 0 0 0 0 0 474 740 877 2,091
187 AddStreetParking 0 0 0 0 0 170 1,841 0 0 0 0 2,011
188 AddFerryAccessPedestrian 0 0 0 36 59 354 399 263 217 308 250 1,886
189 MtbScaleOverlay 0 0 0 0 0 0 0 0 0 1,004 546 1,550
190 AddTents 0 0 0 0 0 0 0 0 0 0 1,474 1,474
191 AddBikeRepairAvailability 0 0 0 0 0 0 259 372 283 219 141 1,274
192 AddFirewood 0 0 0 0 0 0 0 0 0 98 1,132 1,230
193 AddHotWater 0 0 0 0 0 0 0 0 0 1 1,206 1,207
194 AddChargingStationBicycleCapacity 0 0 0 0 0 0 0 0 0 153 978 1,131
195 AddSummitRegister 0 0 0 0 16 87 168 199 225 207 196 1,098
196 AddParkingCharge 0 0 0 0 0 0 0 0 0 3 1,079 1,082
197 AddCaravans 0 0 0 0 0 0 0 0 0 0 989 989
198 AddTowerAccess 0 0 0 0 0 0 0 0 0 127 789 916
199 AddFerryAccessHgv 0 0 0 0 0 0 0 0 0 0 879 879
200 AddMopedAccess 0 0 0 0 0 0 0 0 93 103 633 829
201 AddAccessPointRef 0 0 0 0 0 0 6 101 199 256 254 816
202 AddSecondHandBicycleAvailability 0 0 0 0 0 0 165 218 158 144 124 809
203 AddParkingLane 0 0 0 0 781 0 0 0 0 0 0 781
204 AddParcelLockerMailIn 0 0 0 0 0 0 0 0 764 6 2 772
205 AddSummitCross 0 0 0 0 0 0 111 172 193 156 132 764
206 AddPoliceType 0 0 0 0 0 150 159 127 180 78 30 724
207 IsBuildingUnderground 0 0 130 549 0 0 0 0 0 0 0 679
208 AddStreetWidth 0 0 0 0 613 0 0 0 0 0 0 613
209 AddBenchMaterial 0 0 0 0 0 0 380 214 0 0 0 594
210 AddOnewayAerialway 0 0 0 0 0 0 0 0 0 0 512 512
211 AddChargingStationSocket 0 0 0 0 0 0 0 0 0 0 509 509
212 AddHousenumberToNodes 0 0 0 0 0 0 481 0 0 0 0 481
213 AddFerryAccessBicycle 0 0 0 0 0 0 0 0 0 0 414 414
214 AddAerialwayBicycleAccess 0 0 0 0 0 0 0 0 0 50 244 294
215 AddBoatRental 0 0 0 0 0 0 0 0 24 134 107 265
216 StreetFurnitureOverlay 0 0 0 0 0 0 0 130 92 0 0 222
217 ParkingConditionOverlay 0 0 0 0 0 0 0 0 0 0 177 177
218 AddBenchHostileArchitecture 0 0 0 0 0 0 0 0 0 0 173 173
219 SmoothnessOverlay 0 0 0 0 0 0 0 0 0 142 0 142
220 AddVendingMachineType 0 0 0 0 0 0 0 0 0 0 141 141
221 AddHousenumberForAll 0 0 0 0 0 111 27 0 0 0 0 138
222 AddPaymentGirocard 0 0 105 30 0 0 0 0 0 0 0 135
223 AddDoctorType 0 0 0 0 0 0 0 0 0 0 122 122
224 AddPaymentMastercard 0 0 94 24 0 0 0 0 0 0 0 118
225 AddPaymentContactless 0 0 94 23 0 0 0 0 0 0 0 117
226 AddPaymentVisa 0 0 90 23 0 0 0 0 0 0 0 113
227 RoadSurfaceOverlay 0 0 0 0 0 0 23 78 0 0 0 101
228 BicycleParkingOperator 0 0 0 0 0 0 0 0 44 27 2 73
229 AddLockable 0 0 0 0 0 0 0 0 0 0 63 63
230 AddFirstAidKitLocation 0 0 0 0 0 0 0 0 0 11 50 61
231 AddRef 0 0 0 0 0 0 55 0 0 0 0 55
232 SpecifyEntrance 0 0 0 0 0 0 0 0 33 21 0 54
233 AddFacadeBuilding 0 0 0 0 0 0 52 2 0 0 0 54
234 AddHandwashing 0 0 0 0 0 0 0 0 0 5 47 52
235 AddShelterCapacity 0 0 0 0 0 0 0 0 0 4 47 51
236 AddContactPhone 0 0 0 0 15 27 5 0 0 0 0 47
237 AddProhibitedForMoped 0 0 0 0 0 0 0 0 45 0 0 45
238 AddParkingCapacityDisabled 0 0 0 0 0 0 43 0 0 0 0 43
239 PathSurfaceOverlay 0 0 0 0 0 0 17 23 0 0 0 40
240 AddMotorcycleParkingSurface 0 0 0 0 0 0 0 0 0 0 40 40
241 AddPublicTransport 0 0 0 0 0 0 39 0 0 0 0 39
242 CheckExistencePhone 0 0 0 0 0 0 0 39 0 0 0 39
243 AddBuildingColor 0 0 0 0 0 0 36 2 0 0 0 38
244 AddCrossingMarkingsType 0 0 0 0 0 0 37 0 0 0 0 37
245 AddDesignation 0 0 0 0 0 0 34 0 0 0 0 34
246 AddGuidepostRef 0 0 0 0 0 0 0 34 0 0 0 34
247 AddWasteDisposalAccess 0 0 0 0 0 0 0 0 5 24 1 30
248 AddGuidepostEle 0 0 0 0 0 0 0 30 0 0 0 30
249 AddChargingStationMotorcar 0 0 0 0 0 0 0 0 0 0 28 28
250 AddSmoothnessinlineskates 0 0 0 0 0 0 0 0 0 0 28 28
251 AddMtbScale 0 0 0 0 0 0 0 25 0 0 0 25
252 AddPlacePhone 0 0 0 0 0 25 0 0 0 0 0 25
253 AddWayLight 0 24 0 0 0 0 0 0 0 0 0 24
254 AddStreetLighting 0 22 0 0 0 0 0 0 0 0 0 22
255 AddGuidepostActivityHiking 0 0 0 0 0 0 0 21 0 0 0 21
256 AddGuidepostName 0 0 0 0 0 0 0 21 0 0 0 21
257 CreatePoiBasedOnAtp 0 0 0 0 0 0 0 0 0 16 1 17
258 AddGuidepostActivity 0 0 0 0 0 0 0 17 0 0 0 17
259 AddPaymentCoins 0 0 14 1 0 0 0 0 0 0 0 15
260 AddTrafficSignalsBlindFeatures 0 0 0 0 14 0 0 0 0 0 0 14
261 AddLightingRoads 0 14 0 0 0 0 0 0 0 0 0 14
262 AddParkingCapacityDisabledNumber 0 0 0 0 0 0 14 0 0 0 0 14
263 AddContactWebsite 0 0 0 0 5 6 1 0 0 0 0 12
264 AddStepsHandrails 0 11 0 0 0 0 0 0 0 0 0 11
265 UniversalSurfaceOverlay 0 0 0 0 0 0 8 0 0 0 0 8
266 AddOpeningHoursAtp 0 0 0 0 0 0 0 0 0 8 0 8
267 AddSidewalkSmoothnessLeft 0 6 0 0 0 0 0 0 0 0 0 6
268 AddSidewalkSmoothnessRight 0 5 0 0 0 0 0 0 0 0 0 5
269 AddHairdresser 0 0 0 0 0 0 0 5 0 0 0 5
270 AddFuelE10 0 0 4 1 0 0 0 0 0 0 0 5
271 MarkCompletedConstruction 0 0 5 0 0 0 0 0 0 0 0 5
272 AddAtmCashin 0 0 0 0 0 0 5 0 0 0 0 5
273 IsMonumentOrMemorial 0 0 0 0 0 0 0 0 0 5 0 5
274 AddQuietHours 0 0 0 0 0 0 4 0 0 0 0 4
275 AddDrinkingWaterFee 0 0 0 0 0 0 0 0 4 0 0 4
276 AddRulesInformationBoard 0 0 0 0 0 0 0 0 0 4 0 4
277 AddFuelLPG 0 0 4 0 0 0 0 0 0 0 0 4
278 AddIsPharmacyDispensing 0 0 0 0 0 0 0 4 0 0 0 4
279 AddFuelHgvDiesel 0 0 1 2 0 0 0 0 0 0 0 3
280 AddLeafType 0 0 0 3 0 0 0 0 0 0 0 3
281 AddReligionToCemetery 0 0 0 0 3 0 0 0 0 0 0 3
282 AddFuelOctane98 0 0 2 1 0 0 0 0 0 0 0 3
283 AddAcceptsMedicineTrash 0 0 0 0 0 0 0 0 1 1 0 2
284 AddTrafficSignalsWaitDuration 0 0 0 0 2 0 0 0 0 0 0 2
285 AddFuelOctane95 0 0 2 0 0 0 0 0 0 0 0 2
286 AddSegregated 0 0 2 0 0 0 0 0 0 0 0 2
287 AddAlsoShopForInsurance 0 0 0 0 0 0 0 0 1 1 0 2
288 AddMonumentName 0 0 0 0 0 0 0 0 0 2 0 2
289 AddBackupGenerator 0 0 0 0 0 0 0 1 0 0 0 1
290 AddBikeChargingStationCapacity 0 0 0 0 0 0 0 0 0 1 0 1
291 DetailPavedRoadSurface 0 1 0 0 0 0 0 0 0 0 0 1
292 LGBTQAccess 0 0 0 0 0 0 0 0 1 0 0 1
293 ShowFixme 0 0 0 0 0 0 0 0 1 0 0 1
294 RemoveDiscordantMaxSpeed 0 0 0 0 0 0 1 0 0 0 0 1
295 AddFuelDiesel 0 0 1 0 0 0 0 0 0 0 0 1
296 AddRailStopRef 0 0 0 0 0 0 1 0 0 0 0 1
297 AddScooterChargingStationCapacity 0 0 0 0 0 0 0 0 0 1 0 1
298 AddBoatLockType 0 0 0 0 0 0 0 0 0 1 0 1
299 AddBusStopType 0 1 0 0 0 0 0 0 0 0 0 1
300 AddWheelchairAccessDogPark 0 0 1 0 0 0 0 0 0 0 0 1
301 AddWater 0 1 0 0 0 0 0 0 0 0 0 1
302 AddWebsiteMenuLinkLocation 0 0 0 0 0 0 0 0 1 0 0 1
303 AddWheelchairAccess 0 1 0 0 0 0 0 0 0 0 0 1
304 AddAerialBothWay 0 0 0 0 0 0 0 0 0 1 0 1
305 AddAdblue 0 0 0 0 1 0 0 0 0 0 0 1
306 VerifyNaptan 0 0 0 0 0 0 1 0 0 0 0 1

Accumulated Edits and Contributors of Top 10 StreetComplete Quests

code
# Top 10 StreetComplete quests with monthly trends
df_top_quests = duckdb.sql("""
WITH top_quests AS (
    SELECT streetcomplete_quest
    FROM (
        SELECT
            streetcomplete_quest,
            COUNT(DISTINCT user_name) as total_contributors
        FROM '../changeset_data/year=*/month=*/*.parquet'
        WHERE created_by = 'StreetComplete'
        GROUP BY streetcomplete_quest
        ORDER BY total_contributors DESC
        LIMIT 10
    )
),
monthly_quest_data AS (
    SELECT 
        year,
        month,
        CONCAT(year, '-', LPAD(CAST(month as VARCHAR), 2, '0')) as months,
        streetcomplete_quest,
        COUNT(DISTINCT user_name) as "Contributors",
        SUM(edit_count) as "Edits"
    FROM '../changeset_data/year=*/month=*/*.parquet'
    WHERE streetcomplete_quest IN (SELECT streetcomplete_quest FROM top_quests)
    GROUP BY year, month, streetcomplete_quest
)
SELECT 
    months,
    streetcomplete_quest,
    "Contributors",
    "Edits",
    SUM("Contributors") OVER (PARTITION BY streetcomplete_quest ORDER BY year, month) as "Accumulated Contributors",
    SUM("Edits") OVER (PARTITION BY streetcomplete_quest ORDER BY year, month) as "Accumulated Edits"
FROM monthly_quest_data
ORDER BY year, month, streetcomplete_quest
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Monthly Contributors by Top 10 StreetComplete Quests",
            label="Monthly Contributors",
            x_col="months",
            y_col="Contributors",
            group_col="streetcomplete_quest",
            query_or_df=df_top_quests,
        ),
        util.FigureConfig(
            title="Monthly Edits by Top 10 StreetComplete Quests",
            label="Monthly Edits",
            x_col="months",
            y_col="Edits",
            group_col="streetcomplete_quest",
            query_or_df=df_top_quests,
        ),
        util.FigureConfig(
            title="Accumulated Contributors by Top 10 StreetComplete Quests",
            label="Accumulated Contributors",
            x_col="months",
            y_col="Accumulated Contributors",
            group_col="streetcomplete_quest",
            query_or_df=df_top_quests,
        ),
        util.FigureConfig(
            title="Accumulated Edits by Top 10 StreetComplete Quests",
            label="Accumulated Edits",
            x_col="months",
            y_col="Accumulated Edits",
            group_col="streetcomplete_quest",
            query_or_df=df_top_quests,
        ),
    ]
)

Map with Total Edits Using StreetComplete

code
df_map = duckdb.sql("""
SELECT
    mid_pos_x as x,
    mid_pos_y as y,
    SUM(edit_count) as z
FROM '../changeset_data/year=*/month=*/*.parquet'
WHERE created_by = 'StreetComplete'
    AND mid_pos_x IS NOT NULL 
    AND mid_pos_y IS NOT NULL
GROUP BY mid_pos_x, mid_pos_y
""").df()

util.show_figure(
    [
        util.FigureConfig(
            title="Global Distribution of StreetComplete Edits",
            x_col="x",
            y_col="y",
            z_col="z",
            query_or_df=df_map,
            plot_type="map",
        )
    ]
)