Dupple quiz country mix: aggregation method
Snapshot extracted 5 November 2026. This script reads an SQLite database supplied through --db; it outputs aggregate country counts only.

#!/usr/bin/env python3
"""Export a privacy-preserving, dated country mix from a read-only quiz SQLite DB.

Only aggregate country counts leave the database. One latest row per normalized
email in the window is used; raw identifiers are never written to the CSV.
"""
import argparse
import csv
import sqlite3
import sys
from datetime import datetime, timezone
from pathlib import Path

parser = argparse.ArgumentParser()
parser.add_argument("--db", required=True)
args = parser.parse_args()
start = int(datetime(2026, 8, 5, tzinfo=timezone.utc).timestamp() * 1000)
end = int(datetime(2026, 11, 4, tzinfo=timezone.utc).timestamp() * 1000)
connection = sqlite3.connect(Path(args.db).resolve().as_uri() + "?mode=ro", uri=True)
query = """
WITH ranked AS (
  SELECT UPPER(TRIM(COALESCE(country, ''))) AS code,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email)) ORDER BY createdAt DESC, id DESC
         ) AS row_number
  FROM QuizResponse
  WHERE createdAt >= ? AND createdAt < ? AND email IS NOT NULL AND TRIM(email) <> ''
)
SELECT code, COUNT(*) FROM ranked WHERE row_number = 1 GROUP BY code ORDER BY COUNT(*) DESC, code ASC
"""
counts = dict(connection.execute(query, (start, end)))
connection.close()
total = sum(counts.values())
unknown = counts.pop('', 0)
known = total - unknown
if not total or not known:
    raise SystemExit('No usable aggregate data')
ranked = sorted(counts.items(), key=lambda item: (-item[1], item[0]))
top = [(code, count) for code, count in ranked[:10] if count >= 500]
other = known - sum(count for _, count in top)
writer = csv.writer(sys.stdout, lineterminator='\n')
writer.writerow(['country_group', 'respondents', 'share_all_pct', 'share_known_pct'])
for code, count in top + [('OTHER_KNOWN', other), ('UNKNOWN', unknown)]:
    writer.writerow([code, count, f'{100 * count / total:.1f}', f'{100 * count / known:.1f}' if code != 'UNKNOWN' else ''])