By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH Sales AS (
SELECT 'Vent' AS Name, 75.998 AS Sales, '2014-12-31' AS Date UNION ALL
SELECT 'Vent', 153.988, '2015-12-31' UNION ALL
SELECT 'Vent', 180.678, '2016-12-31' UNION ALL
SELECT 'Vent', 372.819, '2017-12-31' UNION ALL
SELECT 'DBG', 4046.0, '2014-12-31' UNION ALL
SELECT 'DBG', 4454.0, '2015-12-31' UNION ALL
SELECT 'DBG', 4238.0, '2016-12-31' UNION ALL
SELECT 'DBG', 4371.0, '2017-12-31'
)
SELECT
s1.Name,
s1.Sales,
s1.Date,
s1.Sales || '/' || s2.Sales AS Year1_Growth,
s1.Sales || '/' || s3.Sales AS Year2_Growth,
s1.Sales || '/' || s4.Sales AS Year3_Growth
FROM Sales s1
LEFT JOIN Sales s2
ON s1.Name = s2.Name AND
CAST(SUBSTR(s1.Date, 1, 4) AS int) = CAST(SUBSTR(s2.Date, 1, 4) AS int) + 1
LEFT JOIN Sales s3
ON s1.Name = s3.Name AND
CAST(SUBSTR(s1.Date, 1, 4) AS int) = CAST(SUBSTR(s3.Date, 1, 4) AS int) + 2
LEFT JOIN Sales s4
ON s1.Name = s4.Name AND
CAST(SUBSTR(s1.Date, 1, 4) AS int) = CAST(SUBSTR(s4.Date, 1, 4) AS int) + 3
ORDER BY
s1.Name, s1.Date;
| Name | Sales | Date | Year1_Growth | Year2_Growth | Year3_Growth |
|---|---|---|---|---|---|
| DBG | 4046 | 2014-12-31 | null | null | null |
| DBG | 4454 | 2015-12-31 | 4454.0/4046.0 | null | null |
| DBG | 4238 | 2016-12-31 | 4238.0/4454.0 | 4238.0/4046.0 | null |
| DBG | 4371 | 2017-12-31 | 4371.0/4238.0 | 4371.0/4454.0 | 4371.0/4046.0 |
| Vent | 75.998 | 2014-12-31 | null | null | null |
| Vent | 153.988 | 2015-12-31 | 153.988/75.998 | null | null |
| Vent | 180.678 | 2016-12-31 | 180.678/153.988 | 180.678/75.998 | null |
| Vent | 372.819 | 2017-12-31 | 372.819/180.678 | 372.819/153.988 | 372.819/75.998 |