calculate percentile and use case when in sql

Question

I have a data set

Cashback table

user   | order_amount
-------+------------
raj    | 200
rahul  | 400
sameer | 244
amit   | 654
arif   | 563
raj    | 245
rahul  | 453
amit   | 534
arif   | 634
raj    | 245
amit   | 235
rahul  | 345
arif   | 632

I want to calculate the percentile of each user order amount

and If the percentile of any user's order amount is more than 80 percentile then return 'Yes' else 'No' in new column Big_spender means he is one of the top spenders

output will look like

user   | percentile | Big_Spender
-------+------------+------------
raj    | 50         |     NO
rahul  | 40         |     NO
sameer | 84         |     YES
amit   | 85         |     YES
arif   | 96         |     YES

Answer 1

You can do something like this

SELECT
    id,
    USER,
    SUM(order_amount),
    total_sum.total,
    ROUND(
        (SUM(order_amount) / total * 100)
    ) AS percentage,
    CASE WHEN ROUND(
        (SUM(order_amount) / total * 100)
    ) > 80 THEN 'Yes' ELSE 'No' END as Big_spender
FROM
    new AS t1
JOIN(
    SELECT
        SUM(t2.order_amount) AS total
    FROM NEW AS
        t2
) AS total_sum
GROUP BY
    USER