Method & SQL

-- Goblins and demons: realized hit rate of alternate lines vs the consensus fair probability.
select
    kind,
    stat,
    count(*)                  as n,
    round(avg(hit), 4)        as hit_rate,
    round(avg(p_fair), 4)     as fair_p,
    round(avg(abs(line - std_line)), 1) as avg_offset
from alts
where p_fair is not null
group by all
order by kind, n desc;

-- Line accuracy by stat type at the closing PrizePicks line.
-- bias > 0 means players beat the line on average (line set too low).
select
    stat,
    count(*)                                              as n,
    round(avg(actual - close_line), 2)                    as bias,
    round(avg(abs(actual - close_line)), 2)               as mae,
    round(median(close_line), 1)                          as median_line,
    round(avg(case when actual <> close_line then (actual > close_line)::int end), 4)             as over_rate,
    round(avg(close_p_over), 4)                           as fair_over
from props
where close_p_over is not null
group by stat
order by n desc;

-- More (over) hit rate by line tier. Tiers are terciles of the closing line within each stat,
-- a proxy for player role (high line = featured player, low line = role player).
with t as (
    select *, ntile(3) over (partition by stat order by close_line) as tier
    from props
)
select
    stat,
    case tier when 1 then 'Low line' when 2 then 'Mid line' else 'High line' end as tier,
    count(*)                                  as n,
    round(avg(case when actual <> close_line then (actual > close_line)::int end), 4) as over_rate,
    round(avg(close_p_over), 4)               as fair_over
from t
group by all
order by stat, tier;

-- More hit rate by week and overall (ties excluded), with a binomial standard error.
select
    coalesce(week::varchar, 'All')                                        as week,
    count(*) filter (where actual <> close_line)                          as n,
    round(avg(case when actual <> close_line then (actual > close_line)::int end), 4) as over_rate,
    round(sqrt(0.25 / count(*) filter (where actual <> close_line)), 4)   as se
from props
group by rollup (week)
order by week;

-- Is the sportsbook consensus a good referee? Bucket fair P(over at the PP line), compare to realized.
select
    round(floor(close_p_over * 20) / 20, 2)   as bucket,
    count(*)                                  as n,
    round(avg(close_p_over), 4)               as predicted,
    round(avg(case when actual <> close_line then (actual > close_line)::int end), 4) as realized
from props
where close_p_over is not null
group by all
having count(*) >= 10
order by bucket;

-- Exposure: a prop is "exposed" when the fair probability of either side clears the cheapest
-- entry breakeven (6-pick Flex, ~54.2%). A skilled user can build +EV entries from these alone.
-- Ties void the pick on PrizePicks, so they are excluded from realized hit rates.
with s as (
    select
        'T-24h' as snapshot,
        greatest(t24_p_over, 1 - t24_p_over) as fav_p,
        case when actual = t24_line then null
             when t24_p_over >= 0.5 then (actual > t24_line)::int
             else (actual < t24_line)::int end as fav_hit
    from props where t24_p_over is not null and t24_line is not null
    union all
    select
        'Close',
        greatest(close_p_over, 1 - close_p_over),
        case when actual = close_line then null
             when close_p_over >= 0.5 then (actual > close_line)::int
             else (actual < close_line)::int end
    from props where close_p_over is not null
)
select
    snapshot,
    fav_p >= be()                       as exposed,
    count(*)                            as n,
    round(avg(fav_p), 4)                as fair_fav_p,
    round(avg(fav_hit), 4)              as realized_fav_hit
from s
group by all
order by snapshot desc, exposed desc;

-- Line management between T-24h and close: when a prop was exposed at T-24h, did PrizePicks
-- move the line toward the market before kickoff?
with m as (
    select
        stat,
        greatest(t24_p_over, 1 - t24_p_over) >= be()                  as exposed_t24,
        close_line <> t24_line                                          as moved,
        case when t24_p_over >= 0.5 then close_line > t24_line
             else close_line < t24_line end                             as moved_toward_market,
        abs(close_line - t24_line)                                      as move_size
    from props
    where t24_p_over is not null and t24_line is not null
)
select
    stat,
    exposed_t24,
    count(*)                                       as n,
    round(avg(moved::int), 4)                      as moved_rate,
    round(avg((moved and moved_toward_market)::int), 4) as moved_toward_rate,
    round(avg(case when moved then move_size end), 2)   as avg_move
from m
group by all
order by stat, exposed_t24 desc;

-- Whole-number lines: how often PrizePicks posts them, and how often they tie (voiding the pick).
select
    stat,
    count(*)                                                    as n,
    round(avg((close_line = floor(close_line))::int), 4)        as whole_line_share,
    round(avg(case when close_line = floor(close_line) then (actual = close_line)::int end), 4) as tie_rate_on_whole
from props
group by stat
order by whole_line_share desc, n desc;

-- The most exposed props at close, with how they finished (ties void the pick).
select
    week, game, player, stat,
    close_line                                                  as pp_line,
    close_book_line                                             as book_line,
    case when close_p_over >= 0.5 then 'More' else 'Less' end   as fair_side,
    round(greatest(close_p_over, 1 - close_p_over), 4)          as fair_p,
    actual,
    case when actual = close_line then 'Tie (void)'
         when (close_p_over >= 0.5) = (actual > close_line) then 'Won'
         else 'Lost' end                                        as fair_side_result
from props
where close_p_over is not null
order by greatest(close_p_over, 1 - close_p_over) desc
limit 12;

-- Board alignment with the closest competitor at close.
select
    stat,
    count(*)                                                as n,
    round(avg((close_line = ud_close_line)::int), 4)        as same_line_rate,
    round(avg(close_line - ud_close_line), 2)               as avg_diff,
    round(avg(abs(close_line - close_book_line)), 2)        as avg_abs_gap_to_books
from props
where ud_close_line is not null and close_book_line is not null
group by stat
order by n desc;