--
-- Feed this into psql, e.g. psql -f script.sql postgres
--

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

-- relnotes is the number of '<LI' tags in the release notes
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	256	394274
2002-11-27	7.3	311	453282
2003-11-17	7.4	269	508523
2005-01-19	8.0	236	654437
2005-11-08	8.1	184	630422
2006-12-05	8.2	236	684646
2008-02-04	8.3	243	762697
2009-07-01	8.4	336	939098
2010-09-20	9.0	258	999862
2011-12-30	9.1	193	1069547
\.

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