Calculating an average by range in jumps of a certain quantity with conditions in SQL

Question

I have a table with the serial number of each product, the number of years it has been marketed in the store and the amount of revenue from the product since it has been in the store. I would like to write a query that will calculate the average revenue over a 5 year period based on the amount of his years in the store. In addition I want the calculation to be done only when there are over 3 products in the range of those years.

The sample table:

Product_Number	Years_in_the_store	Revenue_from_the_product
1	0.16	8690
2	0.36	57661
3	0.85	29
4	1	12280
5	1.4	3318
6	1.45	20686
7	1.79	8905

When in practice the table contains 40 rows. The only solution I could think of is to create manual ranges (i.e. between 5 years and 10 and between 10 years and 15 years) but this can be a problem when I have a larger amount of years. Example of my solution:

SELECT AVG(CASE
               WHEN Years_in_the_store BETWEEN 0 AND 5 THEN Revenue_from_the_product
           END) AS Between_0_to_5,
       AVG(CASE
               WHEN Years_in_the_store BETWEEN 5 AND 10 THEN Revenue_from_the_product
           END) AS Between_5_to_10,
       AVG(CASE
               WHEN Years_in_the_store BETWEEN 10 AND 15 THEN Revenue_from_the_product
           END) AS Between_10_to_15,
       AVG(CASE
               WHEN Years_in_the_store BETWEEN 15 AND 20 THEN Revenue_from_the_product
           END) AS Between_15_to_20,
       AVG(CASE
               WHEN Years_in_the_store BETWEEN 20 AND 25 THEN Revenue_from_the_product
           END) AS Between_20_to_25,
       AVG(CASE
               WHEN Years_in_the_store BETWEEN 25 AND 25 THEN Revenue_from_the_product
           END) AS Between_25_to_30
FROM EXDATA

I would be happy for professional help :)

Answer 1

In order to see in which range a poroduct falls you can integer-divide the years by 5. Group by the thus found ranges and add up their revenue and (fractional) years. Then divide revenue by years and multiply by five years to get an average over the full five year time range.

Use HAVING to only consioder ranges with at least three products.

select
  years_in_the_store div 5 * 5 as five_year_range_start,
  sum(revenue_from_the_product) / sum(years_in_the_store) * 5 as avg_revenue
from mytable
group by years_in_the_store div 5 * 5
having   count(*) >= 3;
order by five_year_range_start;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6db31bce5408126b177000255a3a1b2d