how to select non repeated date intervals?

Question

My products table,

year
2014
2013
2012
2011
2010
2009
2008
2007
2006
2005
2004
2003
2002
2001
2000

My Query,

SELECT dt||'-'||dt1 
FROM
(SELECT year as dt, year+4 as dt1
FROM
products 
GROUP BY year);

My output,

2000-2004
2001-2005
2002-2006
2003-2007
2004-2008
2005-2009
2006-2010
2007-2011
2008-2012
2009-2013
2010-2014
2011-2015
2012-2016
2013-2017
2014-2018

expected result,

2000-2004 
2005-2009 
2010-2014

I'm not getting any clue for generating that date interval. Any tips and suggestion of query will be of great help. Thanks

Answer 1

You can subtract a modulus 5 from the year and group by that.

SELECT (year-(year%5))||'-'||(year-(year%5)+4) AS years
FROM products 
GROUP BY year-(year%5)

A modulus N is the remainder when the nearest lower multitude of N is substracted from a number.
For example:

11%5 = 11 - 10 = 1 
8%5 = 8 - 5 = 3

So by subtracting it from the year you get the nearest multitude of 5

2018 - 2018%5 = 2018 - 3 = 2015

Another way to round the year down is to divide & multiply it by 5

SELECT (CAST(year/5 AS INTEGER)*5)||'-'||MAX(year) AS years
FROM products 
GROUP BY CAST(year/5 AS INTEGER)*5;

A test on db<>fiddle here

Answer 2

You can do it using SQLite ROW_NUMBER to create a row number column and use this column for the calculation of which records to be retrieved:

SELECT t.year ||'-'|| (t.year + 4)
FROM (
    SELECT  *, Row_Number() OVER(ORDER BY year) AS RowNumber 
    FROM products
) t
WHERE (t.RowNumber - 1) % 5 = 0 

Demo here