* 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)
| 796 | * elapsed contributes zero to day k by construction. |
| 797 | */ |
| 798 | async function retention(env: Env, range: Range): Promise<ApiResult> { |
| 799 | const batch = await env.DB.batch([ |
| 800 | env.DB.prepare( |
| 801 | `SELECT CAST(julianday(d.day) - julianday(f.first_day) AS INTEGER) AS k, |
| 802 | count(DISTINCT d.machine_id) AS machines |
| 803 | FROM machine_first_seen f |
| 804 | JOIN machine_days d ON d.machine_id = f.machine_id |
| 805 | WHERE f.first_day BETWEEN ? AND ? |
| 806 | AND d.day >= f.first_day |
| 807 | AND d.day <= date(f.first_day, ?) |
| 808 | GROUP BY k`, |
| 809 | ).bind(range.from, range.to, `+${RETENTION_DAYS} days`), |
| 810 | env.DB.prepare( |
| 811 | `SELECT first_day AS day, count(*) AS machines |
| 812 | FROM machine_first_seen WHERE first_day BETWEEN ? AND ? GROUP BY first_day`, |
| 813 | ).bind(range.from, range.to), |
| 814 | env.DB.prepare(`SELECT max(day) AS day FROM machine_days`), |
| 815 | ]); |
| 816 | |
| 817 | const retained = new Map( |
| 818 | rowsOf<{ k: number; machines: number }>(batch[0]).map((r) => [r.k, r.machines ?? 0]), |
| 819 | ); |
| 820 | const cohortDays = rowsOf<{ day: string; machines: number }>(batch[1]); |
| 821 | const cohortSize = cohortDays.reduce((n, r) => n + (r.machines ?? 0), 0); |
| 822 | const latestDay = firstOf<{ day: string | null }>(batch[2])?.day ?? utcDay(Date.now()); |
| 823 | |
| 824 | const rows = []; |
| 825 | for (let k = 0; k <= RETENTION_DAYS; k++) { |
| 826 | // Machines whose first day is early enough that day k has already happened. |
| 827 | const cutoff = addDays(latestDay, -k); |
| 828 | const eligible = cohortDays.reduce((n, r) => (r.day <= cutoff ? n + (r.machines ?? 0) : n), 0); |
| 829 | const back = retained.get(k) ?? 0; |
| 830 | rows.push({ |
| 831 | day: k, |
| 832 | eligible, |
| 833 | retained: back, |
| 834 | rate: eligible > 0 ? back / eligible : null, |
| 835 | }); |
| 836 | } |
| 837 | |
| 838 | return { |
| 839 | body: { |
| 840 | range, |
| 841 | cohort: cohortSize, |
| 842 | window_days: RETENTION_DAYS, |
| 843 | labels: rows.map((r) => `Day ${r.day}`), |
| 844 | datasets: [ |
| 845 | { |
| 846 | label: 'Retained', |
| 847 | data: rows.map((r) => (r.rate === null ? null : Math.round(r.rate * 1000) / 10)), |
| 848 | }, |
| 849 | ], |
| 850 | rows, |
| 851 | }, |
| 852 | cacheControl: CACHE_CONTROL, |
| 853 | }; |
| 854 | } |
| 855 |