How to compare one value in a row to see if it is higher than 75% of all values in the same column?-CodePudding

I have a table that looks like this:

groups  created_utc            score    count_comments  d_posts ups  downs   ratio
group1  2011-07-11T19:05:19Z    6988    3742             56     8530  1572   .42(8530/20286)
group2  2011-04-23T21:29:12Z    10455   4695             512    11756 1303   .58(11756/20286)

Generated from this query:

SELECT *, ups  / SUM(ups) OVER () AS ratio
FROM table
order by ratio desc;

How do I compare each value in ratio column by row to see if that ratio is greater than 75% of all the ratios to create a new flag column, greater_75p?

The new table should look like this (cant get the new col formatted but should be y/n as options):

groups  created_utc            score    count_comments  d_posts ups  downs   ratio                greater_75p
                                                                                                   y 
group1  2011-07-11T19:05:19Z    6988    3742             56     8530  1572   .42(8530/20286)
group2  2011-04-23T21:29:12Z    10455   4695             512    11756 1303   .58(11756/20286)

I tried this query, but get error Scalar subquery produced more than one element:

SELECT *,ups * 100 / SUM(ups) OVER () AS ratio, 
PERCENT_RANK() OVER(order by (SELECT ups * 100 / SUM(ups) OVER () AS ratio from table )) AS greater_75p
FROM table

Not sure what I am doing wrong and how to derive a comparison in percentages within sql?

Thank you in advance.

CodePudding user response：

To get result of percent_rank() you can use common table expression as below:

with cte as
(SELECT *, ups  / SUM(ups) OVER () AS ratio
FROM table) 
select *,(case when percent_rank()over(order by ration) >0.75 then 'yes' else 'no' end) greater_75p from cte

Please clarify the logic for calculating greater_75p column.