Calculate the return rate per customer based on the last three orders

DB-Fiddle

CREATE TABLE sales (
    id int auto_increment primary key,
    customerID VARCHAR(255),
    order_date DATE,
    sales_volume INT,
    returned_volume INT
);

INSERT INTO sales
(customerID, order_date, sales_volume, returned_volume
)
VALUES 
("Customer_01", "2020-01-01", "200", "50"),
("Customer_01", "2020-02-15", "400", "120"),
("Customer_01", "2020-02-21", "300", "0"),
("Customer_01", "2020-03-17", "800", "480"),
("Customer_01", "2020-04-08", "600", "315"),
("Customer_02", "2020-02-02", "500", "175"),
("Customer_02", "2020-02-03", "900", "620"),
("Customer_02", "2020-03-27", "100", "30"),
("Customer_02", "2020-04-15", "375", "120"),
("Customer_03", "2020-08-12", "700", "280"),
("Customer_04", "2020-03-09", "260", "110");

Expected Result:

customerID      return_rate_last_3_orders
Customer_01             46.76                     = (315+480+0) / (600+800+300)
Customer_02             56.00                     = (120+30+620) / (375+100+900)
Customer_03             40.00                     = (280) / (700)
Customer_04             42.31                     = (110) / (260)

In the table above I have orders per customer and their corresponding sales_volume and return_volume.

Now, I want to calculate the return_rate of the last 3 orders per customer.

So far, I came up with this query:

SELECT
customerID,
SUM(sales_volume),
SUM(returned_volume),
(SUM(returned_volume) / SUM(sales_volume)) AS return_rate
FROM sales
GROUP BY 1
LIMIT 3;

But I have no clue how I can apply the limit to the calculation of the return_rate per customer.
How do I have to modify the query to achieve the expected results?

Answer

If I understand correctly, you can use ROW_NUMBER() to enumerate the rows and then filter before aggregating:

SELECT customerID, SUM(sales_volume), SUM(returned_volume),
       (SUM(returned_volume) / SUM(sales_volume)) AS return_rate
FROM (SELECT s.*,
             ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY order_date DESC) as seqnum
      FROM sales s
     ) s
WHERE seqnum <= 3
GROUP BY 1;

Here is a db<>fiddle.

Advertisement

Answer