Trending Posts From Data That Was Already There

A "trending posts" widget reads, from the outside, like it needs its own infrastructure — a click counter, a ranking job, something new to maintain. The blog already had a PageView table, recording every hit for an unrelated analytics dashboard. The widget turned out to be a groupBy and a date filter, not a new system.

The join that made it free

Aggregating PageView by path and matching it back to Post by slug gets a view count per post with no new writes and no new schema. The only real decision was the window — thirty days, to smooth out a single viral hour without going stale — and a floor on how many distinct paths to consider before a post with three views crowds out one with three hundred.

What almost went wrong

The first draft of the query counted every hit, including the maintainer's own repeated visits while writing the post. That's a metric with a name — vanity views — and it's worse than useless for a "trending" widget, because it rewards checking your own work over anyone else's interest. The fix was excluding hits from paths visited by the same session within a short window, the same dedup logic the analytics dashboard already used for "unique visitors."

The actual insight

Most "new feature" requests aren't requests for new data, they're requests for a new view of data a system is already collecting for some other reason. The expensive part of this widget wasn't the aggregation query, it was resisting the instinct to stand up a parallel tracking system before checking whether the existing one already had the answer.