Improve account score calculation
Related to hive_accounts_rank_view
naive definition.
It seems that same data can be generated by using dedicated Postgres functionality: nth_value
what allows to fast query for reputation edge values specific to given score.
select
nth_value(ha.reputation, 200) OVER (ORDER BY ha.reputation DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as min_200,
nth_value(ha.reputation, 1000) OVER (ORDER BY ha.reputation DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as min_1000,
nth_value(ha.reputation, 6500) OVER (ORDER BY ha.reputation DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as min_6500,
nth_value(ha.reputation, 25000) OVER (ORDER BY ha.reputation DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as min_25000,
nth_value(ha.reputation, 100000) OVER (ORDER BY ha.reputation DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as min_100000
from hivemind_app.hive_accounts ha
limit 1
We can even go further and cache such limits in the single-record table (updated at the end of update_account_reputations
) and next define scalar function converting fiven account reputation value to the score level, basing on precomputed (cached) limits.
This should significantly speedup notifications APIs.
This could allow to eliminate notification cache table.