How to compute a conditional percentile in Oracle SQL

Question

I'm currently working with Oracle SQL and I need a logic to extract a particular benchmark value from a data set.

What I have:

Article | Sales
1       | 5
2       | 0
3       | 2
4       | 9
........| ......
119     | 3
120     | 8
121     | 12

What I need: I'm looking for the top 5% percent of the articles and in particular the worst selling one of that set. In order to continue my analysis, I would need the sales amount from that article next to the existing columns.

Assuming that the top 5% of articles sell 9, 12, 14, 14, 17 and 19 times (with 9 being the worst of the top 5%), the table would need to look like this:

Article | Sales | Benchmark
1       | 5     | 9
2       | 0     | 9
3       | 2     | 9
4       | 9     | 9
........| ......| ......
118     | 3     | 9
119     | 8     | 9
120     | 12    | 9

Any advice on how i can accomplish that? Thanks!

Answer 1

One method uses percentile_cont() or percentile_disc(). However, I often do this type of calculation manually:

  select t.*,
         min(case when seqnum >= cnt * 19 / 20 then sales end) over () as benchmark
  from (select t.*,
               row_number() over (order by sales) as seqnum,
               count(*) over () as cnt
        from t
       ) t;