Patent applications, NUTS- 3 regions

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

hugo.blixt@invest.uu.se
Posts: 1
Joined: Mon May 06, 2024 2:35 pm

Patent applications, NUTS- 3 regions

Post by hugo.blixt@invest.uu.se » Wed May 08, 2024 11:40 am

Hi,
I need some help to verify and establish a query to measure patent applications by regions and would appreciate if someone could give me some feedback on the code below.

Most of the cities that I want to compare are situated in EU, but some are in the US. Will the code below be able to accurately estimate the number of patent applications from US cities as well?
I'm afraid of an apples to pears comparison and my goal is to have data similar to what EPO produces but over NUTS-3 regions. ( https://www.epo.org/en/about-us/statist ... a-download)

EPO define their search as: European patent applications include direct European applications and international (PCT) applications that entered the European phase during the reporting period.

Thank you!

Code: Select all

SELECT	tls904_nuts.nuts 
		,nuts_label
		,count(distinct (case when earliest_publn_year = 2010 then tls201_appln.earliest_pat_publn_id end))as "2010"
		,count(distinct (case when earliest_publn_year = 2011 then tls201_appln.earliest_pat_publn_id end))as "2011"
		,count(distinct (case when earliest_publn_year = 2012 then tls201_appln.earliest_pat_publn_id end))as "2012"
		,count(distinct (case when earliest_publn_year = 2013 then tls201_appln.earliest_pat_publn_id end))as "2013"
		,count(distinct (case when earliest_publn_year = 2014 then tls201_appln.earliest_pat_publn_id end))as "2014"
		,count(distinct (case when earliest_publn_year = 2015 then tls201_appln.earliest_pat_publn_id end))as "2015"
		,count(distinct (case when earliest_publn_year = 2016 then tls201_appln.earliest_pat_publn_id end))as "2016"
		,count(distinct (case when earliest_publn_year = 2017 then tls201_appln.earliest_pat_publn_id end))as "2017"
		,count(distinct (case when earliest_publn_year = 2018 then tls201_appln.earliest_pat_publn_id end))as "2018"
		,count(distinct (case when earliest_publn_year = 2019 then tls201_appln.earliest_pat_publn_id end))as "2019"
		,count(distinct (case when earliest_publn_year = 2020 then tls201_appln.earliest_pat_publn_id end))as "2020"
		,count(distinct (case when earliest_publn_year = 2021 then tls201_appln.earliest_pat_publn_id end))as "2021"
		,count(distinct (case when earliest_publn_year = 2022 then tls201_appln.earliest_pat_publn_id end))as "2022"
		,count (distinct (tls201_appln.earliest_pat_publn_id)) as "Total"
FROM tls904_nuts
	  join tls206_person on tls904_nuts.nuts = tls206_person.nuts
	  join tls207_pers_appln on tls206_person.person_id = tls207_pers_appln.person_id
	  join tls201_appln on tls207_pers_appln.appln_id = tls201_appln.appln_id
where tls904_nuts.nuts  in ('SE110', 'SE121', 'NO081', 'DK011', 'DE212', 'ES511', 'CH050') and appln_auth = 'EP' and appln_kind = 'A' and applt_seq_nr > 0 and tls201_appln.earliest_publn_year > 2009
and tls904_nuts.nuts_level = 3
group by tls904_nuts.nuts 
		,nuts_label
order by total desc
Hugo


rorasov
Posts: 1
Joined: Thu Jul 04, 2024 8:36 am

Re: Patent applications, NUTS- 3 regions

Post by rorasov » Thu Jul 04, 2024 8:39 am

hugo.blixt@invest.uu.se wrote:
Wed May 08, 2024 11:40 am
Hi, heardle
I need some help to verify and establish a query to measure patent applications by regions and would appreciate if someone could give me some feedback on the code below.

Most of the cities that I want to compare are situated in EU, but some are in the US. Will the code below be able to accurately estimate the number of patent applications from US cities as well?
I'm afraid of an apples to pears comparison and my goal is to have data similar to what EPO produces but over NUTS-3 regions. ( https://www.epo.org/en/about-us/statist ... a-download)

EPO define their search as: European patent applications include direct European applications and international (PCT) applications that entered the European phase during the reporting period.

Thank you!

Code: Select all

SELECT	tls904_nuts.nuts 
		,nuts_label
		,count(distinct (case when earliest_publn_year = 2010 then tls201_appln.earliest_pat_publn_id end))as "2010"
		,count(distinct (case when earliest_publn_year = 2011 then tls201_appln.earliest_pat_publn_id end))as "2011"
		,count(distinct (case when earliest_publn_year = 2012 then tls201_appln.earliest_pat_publn_id end))as "2012"
		,count(distinct (case when earliest_publn_year = 2013 then tls201_appln.earliest_pat_publn_id end))as "2013"
		,count(distinct (case when earliest_publn_year = 2014 then tls201_appln.earliest_pat_publn_id end))as "2014"
		,count(distinct (case when earliest_publn_year = 2015 then tls201_appln.earliest_pat_publn_id end))as "2015"
		,count(distinct (case when earliest_publn_year = 2016 then tls201_appln.earliest_pat_publn_id end))as "2016"

FROM tls904_nuts
	  join tls206_person on tls904_nuts.nuts = tls206_person.nuts
	  join tls207_pers_appln on tls206_person.person_id = tls207_pers_appln.person_id
	  join tls201_appln on tls207_pers_appln.appln_id = tls201_appln.appln_id
where tls904_nuts.nuts  in ('SE110', 'SE121', 'NO081', 'DK011', 'DE212', 'ES511', 'CH050') and appln_auth = 'EP' and appln_kind = 'A' and applt_seq_nr > 0 and tls201_appln.earliest_publn_year > 2009
and tls904_nuts.nuts_level = 3
group by tls904_nuts.nuts 
		,nuts_label
order by total desc
It's good idea


Post Reply