Aggregated patents by country and unassigned country codes

[KenoHaverkamp]

I am trying to get overview statistics on renewable energy patents (PV and Wind) based on the country of residence of the applicant and grouped by each year (2000-2020).

1) Using code from some of the other discussion posts I am using the following SQL code for patents on PV using "cpc_class_symbol like 'Y02E 10/5%'":

Code: Select all

SELECT person_ctry_code, 
COUNT(distinct(case when appln_filing_year = 2000 then tls201_appln.appln_id end)) as '2000',
COUNT(distinct(case when appln_filing_year = 2001 then tls201_appln.appln_id end)) as '2001',
COUNT(distinct(case when appln_filing_year = 2002 then tls201_appln.appln_id end)) as '2002',
COUNT(distinct(case when appln_filing_year = 2003 then tls201_appln.appln_id end)) as '2003',
COUNT(distinct(case when appln_filing_year = 2004 then tls201_appln.appln_id end)) as '2004',
COUNT(distinct(case when appln_filing_year = 2005 then tls201_appln.appln_id end)) as '2005',
COUNT(distinct(case when appln_filing_year = 2006 then tls201_appln.appln_id end)) as '2006',
COUNT(distinct(case when appln_filing_year = 2007 then tls201_appln.appln_id end)) as '2007',
COUNT(distinct(case when appln_filing_year = 2008 then tls201_appln.appln_id end)) as '2008',
COUNT(distinct(case when appln_filing_year = 2009 then tls201_appln.appln_id end)) as '2009',
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when  appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when  appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when  appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when  appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when  appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when  appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when  appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when  appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when  appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when  appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_id) total
FROM   tls201_appln  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
AND appln_filing_year between 2000 and 2020
and applt_seq_nr > 0 
and granted = 'y'
and (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc WHERE cpc_class_symbol like 'Y02E  10/5%'))
group by person_ctry_code
order by total desc

2) I change the "cpc_class_symbol" to "like 'Y02E 10/7%'" for patents relating to wind energy.

Code: Select all

SELECT person_ctry_code, 
COUNT(distinct(case when appln_filing_year = 2000 then tls201_appln.appln_id end)) as '2000',
COUNT(distinct(case when appln_filing_year = 2001 then tls201_appln.appln_id end)) as '2001',
COUNT(distinct(case when appln_filing_year = 2002 then tls201_appln.appln_id end)) as '2002',
COUNT(distinct(case when appln_filing_year = 2003 then tls201_appln.appln_id end)) as '2003',
COUNT(distinct(case when appln_filing_year = 2004 then tls201_appln.appln_id end)) as '2004',
COUNT(distinct(case when appln_filing_year = 2005 then tls201_appln.appln_id end)) as '2005',
COUNT(distinct(case when appln_filing_year = 2006 then tls201_appln.appln_id end)) as '2006',
COUNT(distinct(case when appln_filing_year = 2007 then tls201_appln.appln_id end)) as '2007',
COUNT(distinct(case when appln_filing_year = 2008 then tls201_appln.appln_id end)) as '2008',
COUNT(distinct(case when appln_filing_year = 2009 then tls201_appln.appln_id end)) as '2009',
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when  appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when  appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when  appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when  appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when  appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when  appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when  appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when  appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when  appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when  appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_id) total
FROM   tls201_appln  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
AND appln_filing_year between 2000 and 2020
and applt_seq_nr > 0 
and granted = 'y'
and (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc WHERE cpc_class_symbol like 'Y02E 10/5%'))
group by person_ctry_code
order by total desc

This seems to work for me but just wanted to make sure this is a reasonable to get the information I need or am I missing some crucial aspect about the country origin here?
Later I might change this to subgroups of Y02E 10/5 (541-549) and offshore and onshore wind turbines (Y02E 10727-728)

My main problem is that I noticed there is a very large number of patents where no country code is available for the applicant. Is this due to the information available for applicant, the use of the Y02E classification, or something else which I could address to decrease this number?


Many thanks in advance and happy for any suggestions

[EPO / PATSTAT Support]

Hello Keno,
what you observe is simply due to the fact that many patent offices do not provide the country codes of the applicants (or inventors) to the EPO.
Some researchers have developed methodologies to replenish methodologies to replenish missing country codes. (data-replenishment-of-missing-inventor- ... 377#p34377)
Depending on your needs, you could also count the patent families instead of the applications, that would partly mitigate the problem of missing country codes (any family member having a country code for the applicant would place the family in the relevant country) and you would avoid double counting of inventions. But there might good reasons to count applications.
For coverage on person country codes, kindly see the data completeness post.
mapping-data-completeness-of-patstat-gl ... ls231-7984
Geert Boedt