Title

d

Drew

04/20/2023, 11:59 PMI'm also trying to create a 75 percentile function and here is what I come with so far
I wrote this query and I wasn't able to use the alias for the 0.75, all_count and so on. I'm just learning QuestDB and SQL in general so all this is new to me.

```
WITH ordered_fid AS(SELECT metric_name, values FROM vitals WHERE metric_name = 'FID' ORDER BY values),
calc_data AS (SELECT
metric_name,
last(values) last_item,
count() - 1 all_count,
(0.75 * count() - 1) x,
(0.75 * count() - 1) % 1 r,
round(0.75 * count() - 1) i FROM ordered_fid)
SELECT
*,
CASE
WHEN i = all_count THEN last_item
ELSE ordered_fid[i] + r * (ordered_fid[i + 1] - ordered_fid[i]) -> the issue is here
END as percentile
FROM calc_data;
```

I'm using the formula from below
```
# uses C=1 variant, like percentile_cont
# <https://en.wikipedia.org/wiki/Percentile#The_linear_interpolation_between_closest_ranks_method>
# [1, 2, 3].percentile(0.75)
sorted = map(&block).sort
x = percentile * (sorted.size - 1)
r = x % 1
i = x.floor
if i == sorted.size - 1
sorted[-1]
else
sorted[i] + r * (sorted[i + 1] - sorted[i])
end
```

b

Bolek Ziobrowski

04/21/2023, 7:11 AMSQL is not a procedural language . You can do this type of computation with e.g. window function

```
WITH ordered_fid AS ( SELECT values, row_number() over(order by values) rn FROM vitals WHERE metric_name = 'FID' ),
cnt as ( SELECT count(*) val FROM vitals WHERE metric_name = 'FID' )
select *
from
cnt join
ordered_fid on cnt.val*0.75 = rn
```

but it won't be as efficient as a dedicated function.d

Drew

04/21/2023, 6:16 PMI understand and makes sense!
Thank you very much for your precious help!