Entry is the adjusted close of the video's resolved_trading_day:

• published_at_utc is converted to America/New_York time.
• If that calendar day is an NYSE trading session and the local time is before 16:00 ET, entry is that day's close.
• Otherwise, entry is the next NYSE trading day's close.

Intraday-entry simplification (disclosed): we do not use the exact minute of publication as the fill. We assume a fill at the daily close per the rule above. Actual fills in a live market would differ.

Adjusted-price basis: all returns use split- and dividend-adjusted closes (auto_adjust=True). The entry price is recomputed from the same adjusted series on every snapshot — a stored raw entry price is never treated as ground truth.

A pick is in-scope only if it passes every check below, evaluated at entry date. Checks are applied in order and the first matching exclusion wins. Every excluded pick stores an out_of_scope_reason.

ETFs: exempt from the market-cap rule (check 6), flagged is_etf, and benchmarked normally.

One open position per (channel, ticker), derived by an absolute, replay-safe reducer over picks (never incremental mutation). Opening actions: new_position, added_to_position, high_conviction.

Win predicate: a position wins when alpha_spy_pct > 0 — i.e., its return exceeds SPY over identical dates. An alpha of exactly zero is not a win. Alpha is excess return over the benchmark (not regression alpha).

• Realized set = closed positions, measured at each position's closed_day.
• Unrealized set = open positions, measured as of last_snapshot_at.
• realized_win_rate = wins / closed positions with non-NULL realized alpha; unrealized_win_rate = wins / open positions with non-NULL alpha.
• A position lacking a benchmark leg at its measurement date is excluded from both the numerator and the denominator.
• Averages skip NULLs; a zero-count set yields NULL, never 0.
• Realized and unrealized are never blended; average returns and alphas split the same way (equal-weighted over the respective set).

Consensus counts distinct creators with an in-scope, non-passive position whose opened_day falls within the effective window:

• window_days = least(365, current_date − tracking_since)
• tracking_since = earliest video published_at_utc in the database, updated only downward (never the ingest run timestamp).
• The opening pick must have confidence ≥ 0.6, be in-scope (out_of_scope_reason IS NULL), and be non-passive.
• Positions with status_reason = 'reextraction_removed' are excluded from consensus.
• Consensus counts positions regardless of channels.active.

Canonical labels (used everywhere):

Positions aging out of the window leave consensus but stay in v_creator_stats with full history.

The same opening-pick confidence ≥ 0.6 gate applies to both consensus and the creator leaderboard (v_creator_stats). The pick feed and position listings are not gated — they are factual records.

Creator statistics and consensus aggregates remain unpublished until a human-labeled gold-set accuracy gate passes (precision ≥ 0.90, recall ≥ 0.80).

When a re-extraction removes a previously extracted recommendation, the affected still-open position is closed with status_reason = 'reextraction_removed' and a closed_day.

• Consensus: such positions are excluded (a retracted recommendation never counts toward consensus).
• Creator stats: such positions are counted as realized (they carry a closed_day and land in the realized set).
• A retracted recommendation can never re-open a position; a new open after such a close requires an opening-action pick with resolved_trading_day ≥ the re-extraction closed_day.

Evidence quotes are limited to ≤ 200 characters. Longer quotes are truncated to 197 characters plus "..." at persistence, and the database enforces the 200-character limit with a check constraint.

Entry price

Split- and dividend-adjusted close of the pick's resolved trading day; recomputed from the same adjusted series on every snapshot.

Resolved trading day

NYSE trading day derived from published_at_utc → NY time: before 16:00 ET on a trading day uses that day's close, otherwise the next trading day.

SPY/QQQ alpha

Excess return over SPY (primary) or QQQ (secondary) on identical dates: alpha = pick return − benchmark return. Win when alpha_spy_pct > 0.

Exclusion rules — US-listed / $5 / $300M

Picks must be US-listed (NMS/NYQ/ASE or us_market), entry close ≥ $5, and non-ETF market cap (shares_outstanding × entry_close) ≥ $300M at entry date.

Security type

Only equities and ETFs are in-scope; preferreds, warrants, MLPs and other quote types store unsupported_security_type and are excluded.

Confidence gate 0.6

Opening picks with confidence < 0.6 are excluded from consensus (v_consensus_effective) and leaderboard (v_creator_stats) but remain in the pick feed as factual records.

Effective window

Counting window for consensus: window_days = least(365, current_date − tracking_since) where tracking_since is the earliest video published_at_utc in the DB.

Realized vs unrealized

Realized = closed positions measured at closed_day; unrealized = open positions as of last_snapshot_at. Win rates and averages never blend the two; NULL means zero count, not 0.