Percentile rank over multiple partitions averages

I'd compute the averages and join it to the original table. To rank with respect to the averages, I added a placeholder row to hold the original row.

select * from (
  select *, PERCENT_RANK() OVER(PARTITION BY id order by avg_cnt) AS percentile1 from (
    select 
        t2.*, 
        t1.avg_tier, 
        case t1.avg_tier == "original_row" when true 
            then t2.cnt else t1.avg_cnt end as avg_cnt
    from YOUR_TABLE t2
    join (
      -- find averages and add a placeholder row 
      select tier as avg_tier, avg(cnt) as avg_cnt from YOUR_TABLE group by 1 
      union
      select "original_row", -1 as avg_cnt  -- placeholder row
    ) t1
  ) t3
) t4
where avg_tier="original_row"; -- filter the original row

Before using PERCENT_RANK(), this is what the joined table looks like.

...

Try something like this. First you use CTE to compute averages, then use it to obtain the desired percentile ranks.

Update: I also added a correction to deal with ties.

with cte as
(
select tier, avg(cnt) avg_cnt
from mytable
group by tier
)
select id,cnt,tier,
((select cast(count(*) as double) from cte where t.cnt > avg_cnt) 
+ (select cast(count(*)/2 as double) from cte where t.cnt = avg_cnt) 
)
/ (select cast(count(*) as double) from cte)
from mytable t

I don't have hive in my machine. do we have crossapply in Hive or equivalent.

Can try something like this and apply rant on avg percentage.

select t1.*, 1.00*t1.cnt/a.avg_cnt
from test t1
cross apply (select tier, avg(cnt) avg_cnt
             from test t2 
             where t1.tier=t2.tier
             group by tier) A

Here's a solution with one CTE, and one Window. I've named the main table tests.

with ranks as (
  select 
    avg(cnt) as t1,
    lead(avg(cnt)) over w as t2,
    percent_rank() over w as percentile2
  from (select * from tests union select null, -1, null) as n
  group by tier
  window w as (order by avg(cnt))
)
select tests.*, percentile2
from tests 
join ranks 
  on (tests.cnt > t1 and (tests.cnt <= t2 or t2 is null))

Results:

| id  | cnt  | tier   | percentile2        |
| --- | ---- | ------ | ------------------ |
| 1   | 100  | gold   | 0                  |
| 2   | 200  | silver | 0                  |
| 3   | 300  | bronze | 0                  |
| 4   | 400  | bronze | 0.3333333333333333 |
| 5   | 500  | bronze | 0.6666666666666666 |
| 6   | 600  | gold   | 0.6666666666666666 |
| 7   | 700  | silver | 0.6666666666666666 |
| 8   | 800  | silver | 1                  |
| 9   | 900  | silver | 1                  |
| 10  | 1000 | silver | 1                  |

This SQL is using Postgres syntax, but it should still work in Hive.

The idea is that you first create your ranks by averaging the tier counts and adding an additional dummy row so that it creates 4 ranks instead of 3. That's the union. The lead makes it so that each row in the CTE contains the lower and upper bounds of the rank (t1 and t2).

Then it's just a matter of joining on that CTE where the cnt falls between t1 and t2.