SQL - Split data into quantiles based on sum

Question

I have two columns, column 1 has product, column 2 has sales. I want to sort the table by sales descending. Total sales value/4 will give me quarter value.

I want to then create a group based on this quarter value. Q1- 25% of data, Q2 - 50% of data, Q3 -75% of data, Q4 - 100%

Name the group - Q1, Q2, Q3, Q4 and omit any rows with zero sales. Q1 then would be my highest selling products by value, Q2, second highest etc.

How to do it in sql ?

Sample table -

Expected output -

I tried a case when statement, but unlike the sample I have 1 million rows of data that needs to be grouped, hence hardcoding numbers in case when does not help, need some expert sql help here !

Answer 1

You can achieve this with NTILE(). Assuming your data is actually like this, rather than grouped, you can use the following, but you can modify with GROUP BY and HAVING if these are summations. But that will return the records in the reverse Quartile. To accommodate your order you can subtract the quartile from 5.

SELECT
    product, 
    5 - NTILE(4) OVER (ORDER BY sales)
FROM
    mytable
WHERE 
   sales > 0 
ORDER BY
    sales DESC