How to get the percentile of a sum in Postgres?

Question

I have a table in Postgres that has daily rain data. How can I get the percentile of the annual amount of rain over the years?

The table I have is like this:

Date	Rain
01/02/2020	4.2
02/02/2020	2.3
03/02/2020	3.7
...	...
01/03/2020	2.1
02/03/2020	3.8

I’m trying to use some variations of SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY chuvas.chuva) FROM chuvas; with SUM(chuva) but I was unsuccessful.

Answer 1

You can make a subselect within the PERCENTILE_CONT. It would be something like:

SELECT percentile_cont(0.25) WITHIN GROUP( ORDER BY chv.soma_chuvas ) 
FROM (SELECT data_chuva, sum(chuva) as soma_chuva  FROM chuvas  GROUP BY data_chuva) as chv