--
-- Feed this into psql, e.g. psql -f script.sql postgres
--
-- generate feature count using /pgdev/doc/major_release_itemcount

CREATE TEMPORARY TABLE pg_release (
	reldate		DATE,
	version 	TEXT,
	relnotes	INTEGER,
	lines		INTEGER);

-- relnotes is the number of '<LI' tags in the release notes
-- with the major features duplicate entries removed
COPY pg_release FROM STDIN;
1994-03-17	4.2	\N	250872
1995-09-05	1.0	\N	172470
1996-02-23	1.01	\N	179463
1996-11-04	1.09	\N	178976
1997-01-29	6.0	\N	189399
1997-06-08	6.1	\N	200709
1997-10-02	6.2	\N	225848
1998-03-01	6.3	\N	260809
1998-10-30	6.4	\N	297918
1999-06-09	6.5	\N	331278
2000-05-08	7.0	\N	383270
2001-04-13	7.1	\N	410500
2002-02-04	7.2	250	394274
2002-11-27	7.3	305	453282
2003-11-17	7.4	263	508523
2005-01-19	8.0	230	654437
2005-11-08	8.1	174	630422
2006-12-05	8.2	215	684646
2008-02-04	8.3	214	762697
2009-07-01	8.4	314	939098
2010-09-20	9.0	237	999862
2011-09-12	9.1	203	1069547
2012-09-10	9.2	238	1148192
2013-09-09	9.3	177	1195627
2014-12-18	9.4	211	1261024
2016-01-07	9.5	193	1340005
2016-09-29	9.6	214	1380458
2017-10-05	10	189	1495196
2018-10-18	11	170	1562537
2019-10-03	12	180	1616912
2020-09-24	13	178	1656030
\.

-- Use WITH to generate the LAG value once
WITH pg_release2 AS (
	SELECT 	version, reldate, 
		reldate - LAG(reldate) OVER r AS "days",
		relnotes, lines,
		LAG(lines) OVER r AS "prev"
	FROM pg_release
	WINDOW r AS (ORDER BY reldate)
	ORDER BY reldate
)
SELECT version, reldate, round(days / 30.5) AS "months",
       relnotes, lines, lines - prev AS "change",
       100 * (lines - prev) / prev AS "% change"
FROM pg_release2
UNION ALL
SELECT	'Averages', NULL, round(AVG(days) / 30.5), round(AVG(relnotes)), NULL, NULL,
	AVG(100 * (lines - prev) / prev)::numeric(4,2) AS "average change %"
FROM pg_release2
ORDER BY reldate NULLS LAST;
