Applications disappearing across PATSTAT edictions

Interesting fact, especially if you have to run periodical reports: new edictions in PATSTAT not only add new application but a small number of existign applications disappear across edictions.

Below query counts patents disappearing. Results are listed in the table below for offices/appln_kind with more than 1000 disappearing applications

APPLN_AUTH	APPLN_KIND	EARLIEST_PUBLN_year	Count_APPLN_ID
'AU'	'A'	9999	1142
'AU'	'D'	1968	1067
'AU'	'D'	1969	1351
'AU'	'D'	1970	1458
'AU'	'D'	1971	1491
'AU'	'D'	1972	1389
'AU'	'D'	1973	7322
'AU'	'D'	1974	7474
'AU'	'D'	1975	4478
'AU'	'D'	1976	2554
'AU'	'D'	1977	4680
'AU'	'D'	1978	7522
'AU'	'D'	1979	11628
'AU'	'D'	1980	9765
'AU'	'D'	1981	7680
'AU'	'D'	1982	7652
'AU'	'D'	1983	7804
'AU'	'D'	1984	7995
'AU'	'D'	1985	8747
'AU'	'D'	1986	9701
'AU'	'D'	1987	9809
'AU'	'D'	1988	10245
'AU'	'D'	1989	6807
'AU'	'D'	1990	11410
'AU'	'D'	1991	10272
'AU'	'D'	1992	8128
'AU'	'D'	1993	5961
'AU'	'D'	1994	7735
'AU'	'D'	1995	9861
'AU'	'D'	1996	12173
'AU'	'D'	1997	11543
'AU'	'D'	1998	15028
'AU'	'D'	1999	15274
'AU'	'D'	2000	15162
'AU'	'D'	2001	11687
'AU'	'D'	2002	3623
'KR'	'A'	2005	1956
'KR'	'A'	2006	2954

Some offices show a systematic decrease of number of applications

office	app kind	earl pub date	n apps 2018a	n apps 2016b	Colonna1
'IT'	'A'	2010	9040	9257	-2%
'IT'	'A'	2011	9411	9681	-3%
'IT'	'A'	2012	8913	9152	-3%
'IT'	'A'	2013	8858	9180	-4%
'IT'	'A'	2014	8234	8494	-3%
'IT'	'A'	2015	3833	3865	-1%

SELECT
  a.APPLN_AUTH,
  a.APPLN_KIND,
  a.EARLIEST_PUBLN_year,
  Count(DISTINCT a.APPLN_ID) AS Count_APPLN_ID
FROM
  patstat2016b.tls201_appln a
  LEFT JOIN patstat.tls201_appln b ON b.APPLN_ID = a.APPLN_ID
WHERE
  b.APPLN_ID IS NULL
GROUP BY
  a.APPLN_AUTH,
  a.APPLN_KIND,
  a.EARLIEST_PUBLN_year,
  b.APPLN_ID