35 lines
1.1 KiB
Python
35 lines
1.1 KiB
Python
"""Trending recommendation heuristic service.
|
|
|
|
docs/planning/discovery.md (task 33706f401ad9).
|
|
"""
|
|
|
|
from sqlalchemy import case, func, select, text
|
|
from app.models.world_visit import WorldVisit
|
|
|
|
|
|
def get_trending_subquery(days: int = 7):
|
|
"""Build a subquery that computes a decay-weighted trending score per world_id.
|
|
|
|
Decay weights:
|
|
- Visited within 24 hours: weight 3.0
|
|
- Visited within 3 days: weight 2.0
|
|
- Visited within 7 days: weight 1.0
|
|
"""
|
|
trending_weight = case(
|
|
(WorldVisit.visited_at >= func.now() - text("interval '1 day'"), 3.0),
|
|
(WorldVisit.visited_at >= func.now() - text("interval '3 days'"), 2.0),
|
|
else_=1.0,
|
|
)
|
|
|
|
interval_str = f"interval '{days} days'"
|
|
subq = (
|
|
select(
|
|
WorldVisit.world_id.label("trending_world_id"),
|
|
func.coalesce(func.sum(trending_weight), 0.0).label("trending_score"),
|
|
)
|
|
.where(WorldVisit.visited_at >= func.now() - text(interval_str))
|
|
.group_by(WorldVisit.world_id)
|
|
.subquery("trending_scores")
|
|
)
|
|
return subq
|