Get weekly usage stats from DNF-based Linux distros

dnf-count.ts

// Download totals.db from https://data-analysis.fedoraproject.org/csv-reports/countme/
//
// curl -fsSL https://bun.sh/install | bash
// bun run dnf-count.ts

import { Database } from "bun:sqlite";

const db = new Database("totals.db");

const query = db.query(`
  SELECT 
    date(julianday('1970-01-05') + weeknum * 7) as week_date,
    os_name,
    SUM(max_hits) as weekly_users
  FROM (
    SELECT weeknum, os_name, os_version, os_variant, os_arch, sys_age, MAX(hits) as max_hits
    FROM countme_totals
    GROUP BY weeknum, os_name, os_version, os_variant, os_arch, sys_age
  )
  WHERE week_date = (SELECT date(julianday('1970-01-05') + MAX(weeknum) * 7) FROM countme_totals)
  GROUP BY os_name
`);

const raw = query.all() as { week_date: string; os_name: string; weekly_users: number }[];

const mapping: Record<string, string> = {
  "Fedora Linux": "Fedora",
  "Fedora": "Fedora",
  "Fedora Linux Asahi Remix": "Fedora",
  "CentOS Stream": "CentOS",
  "CentOS Linux": "CentOS",
  "": "Unknown / Generic"
};

const merged = raw.reduce((acc, curr) => {
  const name = mapping[curr.os_name] || curr.os_name;
  acc[name] = (acc[name] || 0) + curr.weekly_users;
  return acc;
}, {} as Record<string, number>);

const top = Object.entries(merged)
  .sort(([, a], [, b]) => b - a)
  .slice(0,20)

const intl = new Intl.NumberFormat("en-us");

console.log("Cleaned Weekly User Base:");
console.table(top.map(([name, users]) => ({ distro: name, weekly_users: intl.format(users) })));

❯ bun run index.ts
Cleaned Weekly User Base:
┌────┬──────────────────────────┬──────────────┐
│    │ distro                   │ weekly_users │
├────┼──────────────────────────┼──────────────┤
│  0 │ Unknown / Generic        │ 12,117,507   │
│  1 │ Rocky Linux              │ 8,454,762    │
│  2 │ AlmaLinux                │ 6,606,103    │
│  3 │ Fedora                   │ 5,226,865    │
│  4 │ Red Hat Enterprise Linux │ 3,381,752    │
│  5 │ CentOS                   │ 3,003,416    │
│  6 │ Oracle Linux Server      │ 825,939      │
│  7 │ CloudLinux               │ 511,530      │
│  8 │ Alibaba Cloud Linux      │ 166,318      │
│  9 │ Bazzite                  │ 155,476      │
│ 10 │ Anolis OS                │ 132,686      │
│ 11 │ MIRACLE LINUX            │ 21,780       │
│ 12 │ Amazon Linux             │ 15,609       │
│ 13 │ Bluefin                  │ 11,960       │
│ 14 │ Debian GNU/Linux         │ 11,559       │
│ 15 │ Ultramarine Linux        │ 7,510        │
│ 16 │ Aurora                   │ 7,377        │
│ 17 │ secureblue               │ 4,864        │
│ 18 │ AlmaLinux Kitten         │ 4,412        │
│ 19 │ Switchvox PBX 104633     │ 4,337        │
└────┴──────────────────────────┴──────────────┘