Function retention

telemetry-dashboard/src/api.ts:704–760

* For machines first seen in the range, the share still active k days later. * * Read entirely off `machine_days` + `machine_first_seen`, neither of which the * retention purge touches, so this answers for any range in history. * * The denominator is per-k, not the whole cohort: a machine first

(env: Env, range: Range)

 * elapsed contributes zero to day k by construction.
 */
async function retention(env: Env, range: Range): Promise<ApiResult> {
  const batch = await env.DB.batch([
    env.DB.prepare(
      `SELECT CAST(julianday(d.day) - julianday(f.first_day) AS INTEGER) AS k,
              count(DISTINCT d.machine_id) AS machines
         FROM machine_first_seen f
         JOIN machine_days d ON d.machine_id = f.machine_id
        WHERE f.first_day BETWEEN ? AND ?
          AND d.day >= f.first_day
          AND d.day <= date(f.first_day, ?)
        GROUP BY k`,
    ).bind(range.from, range.to, `+${RETENTION_DAYS} days`),
    env.DB.prepare(
      `SELECT first_day AS day, count(*) AS machines
         FROM machine_first_seen WHERE first_day BETWEEN ? AND ? GROUP BY first_day`,
    ).bind(range.from, range.to),
    env.DB.prepare(`SELECT max(day) AS day FROM machine_days`),
  ]);

  const retained = new Map(
    rowsOf<{ k: number; machines: number }>(batch[0]).map((r) => [r.k, r.machines ?? 0]),
  );
  const cohortDays = rowsOf<{ day: string; machines: number }>(batch[1]);
  const cohortSize = cohortDays.reduce((n, r) => n + (r.machines ?? 0), 0);
  const latestDay = firstOf<{ day: string | null }>(batch[2])?.day ?? utcDay(Date.now());

  const rows = [];
  for (let k = 0; k <= RETENTION_DAYS; k++) {
    // Machines whose first day is early enough that day k has already happened.
    const cutoff = addDays(latestDay, -k);
    const eligible = cohortDays.reduce((n, r) => (r.day <= cutoff ? n + (r.machines ?? 0) : n), 0);
    const back = retained.get(k) ?? 0;
    rows.push({
      day: k,
      eligible,
      retained: back,
      rate: eligible > 0 ? back / eligible : null,
    });
  }

  return {
    body: {
      range,
      cohort: cohortSize,
      window_days: RETENTION_DAYS,
      labels: rows.map((r) => `Day ${r.day}`),
      datasets: [
        {
          label: 'Retained',
          data: rows.map((r) => (r.rate === null ? null : Math.round(r.rate * 1000) / 10)),
        },
      ],
      rows,
    },
    cacheControl: CACHE_CONTROL,
  };
}