Celes Renata
32 lines
1.5 KiB
SQL
32 lines
1.5 KiB
SQL
-- View: signal_hit_rate
|
|
-- Daily summary of signal accuracy across all symbols and model versions.
|
|
-- Designed for the Superset prediction accuracy dashboard.
|
|
-- Requirements: 10.1, 10.2, 10.3
|
|
-- Design ref: Section 9.2 (prediction confidence vs realized move)
|
|
|
|
CREATE OR REPLACE VIEW lakehouse.stonks.signal_hit_rate AS
|
|
SELECT
|
|
pvo.dt,
|
|
pvo.model_version,
|
|
COUNT(*) AS total_predictions,
|
|
COUNT(CASE WHEN pvo.outcome = 'correct' THEN 1 END) AS correct_predictions,
|
|
COUNT(CASE WHEN pvo.outcome = 'incorrect' THEN 1 END) AS incorrect_predictions,
|
|
COUNT(CASE WHEN pvo.outcome = 'neutral' THEN 1 END) AS neutral_predictions,
|
|
-- Hit rate
|
|
CAST(
|
|
COUNT(CASE WHEN pvo.outcome = 'correct' THEN 1 END) AS DOUBLE
|
|
) / NULLIF(COUNT(*), 0) AS hit_rate,
|
|
-- Average confidence of correct vs incorrect
|
|
AVG(CASE WHEN pvo.outcome = 'correct' THEN pvo.predicted_confidence END)
|
|
AS avg_confidence_correct,
|
|
AVG(CASE WHEN pvo.outcome = 'incorrect' THEN pvo.predicted_confidence END)
|
|
AS avg_confidence_incorrect,
|
|
-- Average realized move magnitude
|
|
AVG(ABS(pvo.actual_move_pct)) AS avg_abs_move_pct,
|
|
AVG(pvo.actual_move_pct) AS avg_move_pct
|
|
FROM
|
|
lakehouse.stonks.prediction_vs_outcome pvo
|
|
GROUP BY
|
|
pvo.dt,
|
|
pvo.model_version;
|