How to apply NTILE(4) using range of column values?

Question

Would like to use NTILE to see the distribution of countries by forested land percent of total land area. The range of values in the column I'd like to use is from 0.00053 to very close to 98.25, and countries are not evenly distributed across the quartiles implied by that range, i.e 0 to 25, 25 to 50, 50 to 75, and 75 to 100 approximately. Instead, NTILE is just dividing the table into four groups with the same number of rows. How do I use NTILE to assign quantiles based on values?

SELECT country, forest, pcnt_forest,
       NTILE(4) OVER(ORDER BY pcnt_forest) AS quartile
FROM percent_forest

Answer 1

You can use a case expression:

select pf.*,
       (case when pcnt_forest < 0.25 then 1
             when pcnt_forest < 0.50 then 2
             when pcnt_forest < 0.75 then 3
             else 4
        end) as bin
from percent_forest pf;

Or, even simpler, use arithmetic:

select pf.*,
       floor(pcnt_forest * 4) + 1 bin
from percent_forest pf;

I would not use the term "quartile" for this column. A quartile implies four equal sized bins (or at least as close as possible given duplicate values).