bvandeusen
9ad4343c76
The verify half of the tune→verify loop (#1251), on the same admin Tuning page as the knobs: - RecommendationWeeklyTrends: weekly per-source outcomes aggregated across all users (the knobs are global, so judging a turn needs global outcomes — rows carry rates only, no track/user identity), with a taste-hit count per bucket: plays whose track's artist has a positive weight in the player's current taste profile. That's the "cheap recompute" reading — retroactive over the whole window, at the cost of profile drift. - GET /api/admin/recommendation-trends?weeks=N (default 12, cap 52): per-family weekly series (skip rate, sample-weighted completion, taste-hit rate) plus the tuning-audit markers inside the window. - Web: sparkline table under the tuning cards — skip rate per week on a shared axis with dashed ticks at knob turns, latest-week columns, window taste-hit rate, low-volume rows dimmed as anecdote, and a plain-text list of the window's tuning changes. Also fixes the revive unused-parameter lint on the tuning GET handler that failed CI run 1903 on the previous commit. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01TsF3cNoKrqCYsU78cXC8U6
59 lines
2.7 KiB
SQL
59 lines
2.7 KiB
SQL
-- Recommendation observability (#796 phase 4). Per-source play outcomes so the
|
|
-- operator can see whether each recommendation surface is landing and tune the
|
|
-- taste weights. Source is stamped on play_events when a play is launched from
|
|
-- a recommendation surface; NULL means the user picked the track manually —
|
|
-- those rows are INCLUDED here as the baseline control group the surfaces are
|
|
-- judged against (milestone #127: delta-vs-baseline is what makes the numbers
|
|
-- actionable). Raw source strings are bucketed into stable surface families in
|
|
-- the Go handler; completion_n is carried so family merges can weight
|
|
-- avg_completion correctly.
|
|
|
|
-- name: RecommendationWeeklyTrends :many
|
|
-- Weekly per-source outcome series for the tuning lab's trend view
|
|
-- (#1251). Aggregated across ALL users: the tuning knobs are global,
|
|
-- so judging a knob turn needs global outcomes — rows carry rates
|
|
-- only, no track or user identity. NULL-source (manual) rows are
|
|
-- included as the baseline family.
|
|
--
|
|
-- taste_hits counts plays whose track's artist has a positive weight
|
|
-- in that user's CURRENT taste profile — the "cheap recompute" option:
|
|
-- retroactive over the whole window, at the cost of drift (the profile
|
|
-- is today's, the play may be weeks old). Good enough to read whether
|
|
-- a surface is feeding taste-fitting tracks.
|
|
-- $1 window in weeks.
|
|
SELECT
|
|
date_trunc('week', pe.started_at)::date AS week_start,
|
|
pe.source,
|
|
count(*)::bigint AS plays,
|
|
count(*) FILTER (WHERE pe.was_skipped)::bigint AS skips,
|
|
count(pe.completion_ratio)::bigint AS completion_n,
|
|
COALESCE(avg(pe.completion_ratio), 0)::float8 AS avg_completion,
|
|
count(*) FILTER (WHERE tpa.artist_id IS NOT NULL)::bigint AS taste_hits
|
|
FROM play_events pe
|
|
JOIN tracks t ON t.id = pe.track_id
|
|
LEFT JOIN taste_profile_artists tpa
|
|
ON tpa.user_id = pe.user_id
|
|
AND tpa.artist_id = t.artist_id
|
|
AND tpa.weight > 0
|
|
WHERE pe.started_at > now() - (sqlc.arg(weeks)::int * INTERVAL '1 week')
|
|
GROUP BY 1, 2
|
|
ORDER BY 1, 2;
|
|
|
|
-- name: RecommendationSourceMetricsForUser :many
|
|
-- $1 user_id, $2 window_days. plays/skips are counts; avg_completion is the
|
|
-- mean completion ratio over the completion_n plays that recorded one.
|
|
-- pick_kind splits For You plays into taste/fresh/unattributed (#1249);
|
|
-- it is NULL for every other source, so those still group to one row.
|
|
SELECT
|
|
pe.source,
|
|
pe.pick_kind,
|
|
count(*)::bigint AS plays,
|
|
count(*) FILTER (WHERE pe.was_skipped)::bigint AS skips,
|
|
count(pe.completion_ratio)::bigint AS completion_n,
|
|
COALESCE(avg(pe.completion_ratio), 0)::float8 AS avg_completion
|
|
FROM play_events pe
|
|
WHERE pe.user_id = $1
|
|
AND pe.started_at > now() - ($2::float8 * INTERVAL '1 day')
|
|
GROUP BY pe.source, pe.pick_kind
|
|
ORDER BY plays DESC;
|