LLMが「食べた」のはStack Overflowの頭だけ：タグ別に質問減少を検証する

"""Aggregate Stack Overflow monthly question volume per tag from SEDE CSV exports.

SEDE (https://data.stackexchange.com/stackoverflow/query/new) has no REST API:
run the SQL below in the browser editor once per tag, click "Download CSV", and
save each as data/{tag}.csv. This script then loads, validates, and compares
pre/post-ChatGPT volume. The per-run CSV link
(https://data.stackexchange.com/stackoverflow/csv/{queryRunId}) is also fetched
opportunistically when a queryRunId is supplied, with retry/backoff.
"""
import sys, time, io, pathlib, urllib.request, urllib.error
import pandas as pd

SQL = """SELECT DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS ym,
       COUNT(*) AS q,
       SUM(CASE WHEN ClosedDate IS NOT NULL THEN 1 ELSE 0 END) AS closed
FROM Posts
WHERE PostTypeId = 1 AND Tags LIKE &
  AND CreationDate >= &
GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) ORDER BY ym;"""

CHATGPT, TAGS = pd.Timestamp("2022-11-01"), ["python", "javascript", "concurrency", "memory-leaks", "race-condition"]


def fetch_csv(tag, run_id=None, retries=3):
    """Load a tag&
    path = pathlib.Path("data") / f"{tag}.csv"
    if path.exists():
        return pd.read_csv(path, parse_dates=["ym"])
    if not run_id:
        raise FileNotFoundError(f"{path} missing; run SQL for '{tag}' and export, or pass a run_id.\n{SQL.format(tag=tag)}")

import pandas as pd, numpy as np
from scipy import stats

# Expected schema: data/so_tag_monthly.csv with columns: month(YYYY-MM), tag, questions
df = pd.read_csv("data/so_tag_monthly.csv", parse_dates=["month"])
df["year"] = df["month"].dt.year

basic = {"python", "javascript", "html", "css", "java", "php", "sql"}
emerging = {"rust", "kubernetes", "terraform", "deno", "wasm", "podman", "svelte"}

# Per-tag: 2020 peak (monthly max) -> 2024 mean, pct change
def peak_to_2024(g):
    peak = g[g.year == 2020].groupby(g.month.dt.to_period("M"))["questions"].sum().max()
    end = g[g.year == 2024]["questions"].mean()
    return (end - peak) / peak * 100 if peak and peak > 0 else np.nan

chg = df.groupby("tag").apply(peak_to_2024).dropna()
b = chg[chg.index.isin(basic)]; e = chg[chg.index.isin(emerging)]

# H1: Wilcoxon (Mann-Whitney, independent groups) basic vs emerging decline
u, p1 = stats.mannwhitneyu(b, e, alternative="less")
print("H1 basic median %:", round(b.median(), 1), "emerging:", round(e.median(), 1),
      "| MWU p=%.4f" % p1, "| basic<=-58%%:", b.median() <= -58)

# H2: segmented regression at ChatGPT release 2022-11
ts = df.groupby(df.month.dt.to_period("M"))["questions"].sum().reset_index()
ts["t"] = np.arange(len(ts)); cp = (ts["month"] >= pd.Period("2022-11")).astype(int)
ts["post"] = cp * (ts["t"] - ts.loc[cp.astype(bool).idxmax(), "t"]).clip(lower=0)
X = np.column_stack([np.ones(len(ts)), ts["t"], ts["post"]])
beta, *_ = np.linalg.lstsq(X, ts["questions"], rcond=None)

git clone https://github.com/example/so-tag-decay.git
cd so-tag-decay
python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt   # pandas, numpy, scipy, matplotlib
# data/ に各タグCSVを配置後（SEDEのSQLは fetch.py 内に同梱）
python fetch.py        # CSV読み込み・検証・前処理
python analyze.py      # Wilcoxon / segmented regression / Spearman を計算