SQL: Return top 1% of values using NTILE function

I have a database with two columns as shown below, ID and COUNT_OF_ACTIONS where I want to return the top 1% of values from the COUNT_OF_ACTIONS – below might not be the best example, but imagine COUNT_OF_ACTIONS going from 0 to 100, so the query should return: 99 and 100 since these counts are >= 99 (top 1% value)

ID  COUNT_OF_ACTIONS
234324  1
15435   2
345432  3
2453    4
2245243 5
54352   6
452345  7
245354  8
3345    9
245245  10

WITH SPACES as (
    SELECT d.ID, COUNT(DISTINCT t.ACTION_ID) as COUNT_OF_ACTIONS
    FROM DATA d
    GROUP BY 1
)

SELECT count(distinct id)
FROM SPACES
NTILE(100) OVER (COUNT_OF_ACTIONS ORDER BY COUNT_OF_ACTIONS DESC) >= 99

I tried to use the NTILE method to get all the values greater than the 99% point but this did not work. Any suggestions would be appreciaed.

Answer

A few changes to your syntax. This works:

Create or replace table t1 (ID varchar(255), COUNT_OF_ACTIONS number(10));
insert into t1 values 
('ID1',1),
('ID2',2),
('ID3',3),
('ID4',4),
('ID5',5),
('ID6',6),
('ID7',7),
('ID8',8),
('ID9',9),
('ID10',10);

with t2 as (
select ID, ntile(100) over (order by COUNT_OF_ACTIONS) as NTILE_100 from t1 )

select * from t2 where ntile_100 >=9;