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

can you please be more specific? lets take ```arif``` as an example - his order sum is 1829, num of order is 3 and average per order is 609.667. with that information how you calculate the percentile — Dean Taler, Sep 01 '21 at 06:38
@LandLord even if it median, you didn't explain how to calculate the percentile. if we go back to ```arif``` example now the median is 632? how did you get percentile of 96? — Dean Taler, Sep 01 '21 at 10:56

score 0 · Answer 1 · answered Sep 01 '21 at 07:01

0

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

answered Sep 01 '21 at 07:01

Rishni Meemeduma

324
3
14

This is for percentage I think not for percentile – Sep 01 '21 at 07:26

calculate percentile and use case when in sql

1 Answers1