How to select a random row from subset of rows that are 20 percent from top(skip), limit 10 percent-CodePudding

I have a table questions, and each question has a difficulty property, I need a random question sorted by difficulty ASC, then skip 20 percent, limit 10 percent, and random of that subset.

Closest that I got:

select *, PERCENT_RANK() over ( order by difficulty ) from questions

Which would add another column to each row with percentage, then I can filter out required with where clause.

Then I realized that PERCENT_RANK is not working the way I want. It calculates the percentage of the row compared to the sum of all difficulties of all rows. but I want a percentage of a ROW_NUMBER compared to the total number of rows.

So what I need is something like this:

select *, PERCENT_RANK() over ( 'ROW NUMBER SOMEHOW HERE' ) from questions order by difficulty

CodePudding user response：

I think if you combine the row_number() and count() analytic (window) functions, you can simply take the rows between 20% and 30%.

Something like this:

with cte as (
  select
    q.*, row_number() over (order by difficulty) as rn,
    count (*) over (partition by 1) as total
  from
    questions q
)
select *
from cte
where rn between total * 0.2 and total * 0.3
order by random()

The order by random() was just my response to your final comment of "and random of that subset."