Anton Tarasenko - 1 year ago 53

SQL Question

I'm trying to put together some simple stats, but stuck with computing the percentile that the author belongs to according to his total scores:

`select`

[by] author,

count(*) count,

sum(score) sum_score,

quantiles(sum(score), 101) percentile_sum_score,

from

[bigquery-public-data:hacker_news.stories]

group by

author

This code returns each author's

`sum(score)`

`percentile_sum_score`

`quantiles(sum(score), 101)`

This happens in BigQuery, and some conventional functions are not available (docs).

Is there a way to get the right statistics?

Answer

You might be looking for PERCENT_RANK() function

Below is possible use for your example

```
SELECT
author,
[count],
sum_score,
PERCENT_RANK() OVER(ORDER BY sum_score DESC) percentile_sum_score,
FROM (
SELECT
[by] author,
COUNT(1) [count],
SUM(score) sum_score,
FROM [bigquery-public-data:hacker_news.stories]
GROUP BY author
)
```

Source (Stackoverflow)