wehub-resource-sync
a789495a98
FreeBSD Smoke / FreeBSD Smoke (x86_64) (push) Has been cancelled
CI / Quality Guardrails (push) Has been cancelled
CI / Build & Test (macos-latest) (push) Has been cancelled
CI / Build & Test (ubuntu-latest) (push) Has been cancelled
CI / Build & Test (windows-latest) (push) Has been cancelled
CI / Format (push) Has been cancelled
CI / PowerShell Syntax (push) Has been cancelled
CI / Windows Cross-Target Check (Linux) (push) Has been cancelled
59 lines
3.0 KiB
SQL
59 lines
3.0 KiB
SQL
-- Current UTC-day and trailing-24h DAU dashboard.
|
|
-- Usage:
|
|
-- wrangler d1 execute jcode-telemetry --remote --file=dau.sql
|
|
--
|
|
-- Note: production `events` never got migration 0005's per-turn columns (D1
|
|
-- caps tables at 100 columns), so turn_end activity lives in `turn_details`
|
|
-- keyed by event_id. The trailing-24h tiers join through it; the today tiers
|
|
-- read the daily_active_users rollup, which is classified at insert time from
|
|
-- the full client payload.
|
|
|
|
WITH today AS (
|
|
SELECT
|
|
COUNT(*) AS raw_today,
|
|
SUM(CASE WHEN meaningful_active > 0 THEN 1 ELSE 0 END) AS meaningful_today,
|
|
SUM(CASE WHEN release_active > 0 THEN 1 ELSE 0 END) AS raw_release_today,
|
|
SUM(CASE WHEN meaningful_release_active > 0 THEN 1 ELSE 0 END) AS meaningful_release_today,
|
|
-- Headline product metric: real users on the release channel, excluding
|
|
-- automated CI traffic (ephemeral runners that mint a fresh id per job).
|
|
SUM(CASE WHEN meaningful_release_active > 0 AND last_is_ci = 0 THEN 1 ELSE 0 END) AS meaningful_release_today_noci,
|
|
SUM(CASE WHEN last_is_ci > 0 THEN 1 ELSE 0 END) AS ci_today
|
|
FROM daily_active_users
|
|
WHERE activity_date = date('now')
|
|
), recent AS (
|
|
SELECT
|
|
e.telemetry_id,
|
|
e.event,
|
|
e.build_channel,
|
|
e.is_ci,
|
|
CASE
|
|
WHEN e.event IN ('session_end', 'session_crash') AND (
|
|
e.turns > 0 OR e.had_user_prompt > 0 OR e.had_assistant_response > 0
|
|
OR e.assistant_responses > 0 OR e.tool_calls > 0 OR e.executed_tool_calls > 0
|
|
OR e.duration_secs > 0 OR e.error_provider_timeout > 0 OR e.error_auth_failed > 0
|
|
OR e.error_tool_error > 0 OR e.error_mcp_error > 0 OR e.error_rate_limited > 0
|
|
OR e.provider_switches > 0 OR e.model_switches > 0
|
|
) THEN 1
|
|
WHEN e.event = 'turn_end' AND (
|
|
td.assistant_responses > 0 OR td.tool_calls > 0 OR td.executed_tool_calls > 0
|
|
OR td.file_write_calls > 0 OR td.tests_run > 0
|
|
) THEN 1
|
|
ELSE 0
|
|
END AS meaningful
|
|
FROM events e
|
|
LEFT JOIN turn_details td ON td.event_id = e.event_id
|
|
WHERE e.event IN ('session_start', 'turn_end', 'session_end', 'session_crash')
|
|
AND e.created_at > datetime('now', '-1 day')
|
|
), trailing_24h AS (
|
|
SELECT
|
|
COUNT(DISTINCT telemetry_id) AS raw_24h,
|
|
COUNT(DISTINCT CASE WHEN meaningful = 1 THEN telemetry_id END) AS meaningful_24h,
|
|
COUNT(DISTINCT CASE WHEN build_channel = 'release' THEN telemetry_id END) AS raw_release_24h,
|
|
COUNT(DISTINCT CASE WHEN build_channel = 'release' AND meaningful = 1 THEN telemetry_id END) AS meaningful_release_24h,
|
|
-- Same headline metric over a rolling 24h window, excluding CI traffic.
|
|
COUNT(DISTINCT CASE WHEN build_channel = 'release' AND is_ci = 0 AND meaningful = 1 THEN telemetry_id END) AS meaningful_release_24h_noci,
|
|
COUNT(DISTINCT CASE WHEN is_ci = 1 THEN telemetry_id END) AS ci_24h
|
|
FROM recent
|
|
)
|
|
SELECT * FROM today, trailing_24h;
|