Efficiently rank in SQL. Rank 1 indexes all values, Rank 2 indexes over users, Rank 3 indexes over heroes (inside user)

SQL rank index

SELECT df.*, @curRank := @curRank + 1 AS rank,
    @prev := @curr,
    @curr := user_id,
    @rank2 := IF(@prev = @curr, @rank2, @rank2+1) AS rank2,
    @rank3 := IF(@prev = @curr, @rank3+1, 1) AS rank3
FROM (
SELECT user_id, itemType,
extractvalue(data, '//heroId') as hero_id, time
FROM fct_generate_hero
ORDER BY user_id, time
) as df, (SELECT @curRank := 0) r, (SELECT @curr := null, @prev := null, @rank2 := 0, @rank3 := 0) r2