Replay statistics (e.g. which replay is your most watched)

[miyakit]

More detailed replay statistics, such as how much each of a players replays has been watched (what’s the most watched replays of that player). Some people also would like to be able to see who watched their replays. https://osu.ppy.sh/community/forums/topics/47412

[cl8n]

I tried to add this one but I don't know how large the relevant tables are, what indices would be required, whether or not ES needs to be used... the query with no change to db structure looks like this

select ...
from `osu_scores_high`
inner join `osu_replays`
  on `osu_scores_high`.`score_id` = `osu_replays`.`score_id`
where `osu_scores_high`.`user_id` = ?
order by `osu_replays`.`play_count` desc

for the design i copied other score lists on user profile and moved things around to make room for a big watch count. this would go below the replay history chart

[peppy]

This will require an index of (user_id, play_count DESC) to be efficient. Right now, user_id is not stored in that table so it's a bit of a process to make this work (would need to be back-filled, and also usages of the table would need to add it going forwards).

Because of the upcoming infrastructure changes that are planned, I'm not sure this is a good time to alter this table to add the user_id requirement, as the addressable score_id is going to be changed.

Highly depends on how sought after this addition is. With only two upvotes on this issue I'm not too sure.

[cl8n]

I found this from https://osu.ppy.sh/community/forums/topics/47412 , just stuck out as an easy one to add (from osu-web perspective, anyway) for how many stars it has. I don't think github activity should be used to gauge what ppl want. this thread is also super old though lol

if it's too much work to add that column and index then I'll save this for later. will be following along with the new score system, looks good so far :eyes:

[peppy]

I'd estimate it'll take around 4-6 hours of my time to make this happen. Probably not going to immediately jump on it, but I've at least added a mention of the feature and its requirements to the infra document I linked.

[peppy]

Since we've moved to new score infrastructure, we probably want a new table to keep track of this. Currently we're not tracking new score replay watches but should be.

Table would be something like:

score_replay_stats (score_id, user_id, watch_count, last_updated) index (user_id, watch_count) index (watch_count)

Latter index could be used for a "most watched scores" ranking page.

Data should also be imported from osu_replays (making sure to move to new score ids).