Lifted from: Timmy Kurian’s Linkedin

Problem:

Given a table “sales” with columns “customer_id”, “date”, and “amount”, write a query to calculate the 90th percentile of sales for each customer in the year 2021.

Sample Tables (CREATE + INSERT):
CREATE TABLE sales (
 customer_id INT,
 date DATE,
 amount DECIMAL(10,2)
);
INSERT INTO sales (customer_id, date, amount)
VALUES
(1, '2021-01-01', 100.00),
(1, '2021-02-01', 200.00),
(1, '2021-03-01', 300.00),
(2, '2021-01-01', 150.00),
(2, '2021-02-01', 250.00),
(2, '2021-03-01', 350.00);

Query:

SELECT customer_id, PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY amount) AS p90_sales
FROM sales
WHERE date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY customer_id;

Explanation:

This query uses the PERCENTILE_CONT function to calculate the 90th percentile of sales for each customer in the year 2021. The WITHIN GROUP clause orders the sales amounts for each customer and the WHERE clause filters for the specified year. The result is grouped by customer_id to show the 90th percentile for each customer.