Patent Fractional Count at NUTS-2 (3) level

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

Simone Robbiano
Posts: 4
Joined: Thu Jun 25, 2020 11:36 am

Patent Fractional Count at NUTS-2 (3) level

Post by Simone Robbiano » Sat Dec 12, 2020 6:57 pm

Hi everybody,
sorry for the question but I'm a beginner in Patstat and SQL language.

I'm conducting an empirical research that requires a panel dataset of patents in fractional count (inventor's region of residence) for EU28 NUTS-2 regions.
Some years ago I developed a simple query to do the same thing with italian NUTS-3 regions (I create the fractional count by means of another software). There is a way to modify such query (in order to have a column with inventor's NUTS-2 region of residence) or someone has some hints to create a new one?

Code: Select all

SELECT APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, N.nuts, N.nuts_level, N.nuts_label, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
	FROM tls904_nuts N
 		 join tls206_person P on N.nuts = P.nuts
 		 join tls207_pers_appln PA on P.person_id = PA.person_id
 		 join tls201_appln APP on PA.appln_id = APP.appln_id
         join tls209_appln_ipc APIPC on APP.appln_id = APIPC.appln_id
         join tls226_person_orig PO on PA.person_id = PO.person_id
         join tls202_appln_title APT on APP.appln_id = APT.appln_id
         join tls211_pat_publn PPB on APP.appln_id = PPB.appln_id
         join tls229_appln_nace2 NA2 on APP.appln_id = NA2.appln_id
         join tls230_appln_techn_field ATF on APP.appln_id = ATF.appln_id
         join tls212_citation CIT on PPB.pat_publn_id = CIT.pat_publn_id
	WHERE N.nuts like 'IT%'
    	AND N.nuts_level=3
		AND APP.appln_filing_date >= '1978-01-01' 
		AND APP.appln_filing_date <= '1978-12-31'
GROUP BY N.nuts, N.nuts_level, N.nuts_label, APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth

Thank you all

Best regards

Simone


EPO / PATSTAT Support
Posts: 255
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Patent Fractional Count at NUTS-2 level

Post by EPO / PATSTAT Support » Mon Dec 14, 2020 3:05 pm

Hello Simone,
NUTS codes are constructed according to a hierarchical system based on the number of digits.
For Italy you can retrieve all the NUTS codes via the query below:

Code: Select all

SELECT  nuts
      ,[nuts_level]
      ,[nuts_label]
  FROM [patstat2020b].[dbo].[tls904_nuts]
  where nuts like 'IT%' 
  order by nuts
NUTS_Italy.xlsx
(1.22 MiB) Downloaded 39 times
If you look at the (sorted) results created with the above query, you can see that under nuts ITC1 for Piemonte, you will find: ITC11:Torino, ITC12:Vercelli, ITC13: Biella, ITC14 :Verbano-Cusio-Ossola etc...
Using a SQL "LEFT" function, you can truncate all the NUTS codes to 4 digits, so that an address that had ITC11:Torino will now be shown as ITC1:Torino.
If you want to replace the "Torino" by "Piemonte", then you will need to join with the tls904_nuts table using the 4-digit IT (nuts_2) codes an their respective names.
The SQL for such table could look like this:

Code: Select all

SELECT  nuts
      ,[nuts_level]
      ,[nuts_label]
  FROM [patstat2020b].[dbo].[tls904_nuts]
  where nuts like 'IT%'and nuts_level <=2
  order by nuts
The forum contains some more posts on how to calculated weighted values on NUTS codes.

Integrating this in your query, it would become something like this: (there are other ways to obtain the same results)

Code: Select all

SELECT APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, 
APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, 
APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, 
APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, 
APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, 
APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, 
P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, 
PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, 
APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, 
APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, left(N.nuts,4) NUTS_2, 
tls904_nuts.nuts_label, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant,
PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id,
CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
	FROM tls904_nuts N
 		 join tls206_person P on N.nuts = P.nuts
 		 join tls207_pers_appln PA on P.person_id = PA.person_id
 		 join tls201_appln APP on PA.appln_id = APP.appln_id
         join tls209_appln_ipc APIPC on APP.appln_id = APIPC.appln_id
         join tls226_person_orig PO on PA.person_id = PO.person_id
         join tls202_appln_title APT on APP.appln_id = APT.appln_id
         join tls211_pat_publn PPB on APP.appln_id = PPB.appln_id
         join tls229_appln_nace2 NA2 on APP.appln_id = NA2.appln_id
         join tls230_appln_techn_field ATF on APP.appln_id = ATF.appln_id
         join tls212_citation CIT on PPB.pat_publn_id = CIT.pat_publn_id
		 left join tls904_nuts  on left(N.nuts,4) = tls904_nuts.nuts and tls904_nuts.nuts like 'IT%' and tls904_nuts.nuts_level <=2 
WHERE p.person_ctry_code = 'IT' 
 	AND APP.appln_filing_date >= '1978-01-01' 
	AND APP.appln_filing_date <= '1978-12-31'
	and appln_auth = 'EP'
GROUP BY N.nuts, N.nuts_level, N.nuts_label, APP.appln_id, APP.appln_auth, 
APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, 
APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, 
APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date,
APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year,
APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, 
APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, 
P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level,
P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, 
PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, 
APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, 
NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, PPB.pat_publn_id, 
PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant,
PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, 
CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
,left(N.nuts,4),tls904_nuts.nuts_label
I did restrict your sample with "appln_auth = 'EP'" because only for EP applications we have good enough address data available for the assignments of the NUTS codes.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Simone Robbiano
Posts: 4
Joined: Thu Jun 25, 2020 11:36 am

Re: Patent Fractional Count at NUTS-2 level

Post by Simone Robbiano » Tue Dec 15, 2020 6:04 pm

Thank you very much, the query works very well.

Best Regards

Simone


Giuseppe Lubrano
Posts: 1
Joined: Fri May 07, 2021 2:04 pm

Re: Patent Fractional Count at NUTS-2 level

Post by Giuseppe Lubrano » Fri May 07, 2021 2:12 pm

Hi Simone,
I am working on innovation topics and I was trying to download the number of patent applications by NUTS3 for Italy. But I have not knowledge of SQL scripts. With your commmand I saw it was possible to download at that level. Could you please share the command for downloading at NUTS 3 level.

Kind regards

Giuseppe Lubrano


EPO / PATSTAT Support
Posts: 255
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Patent Fractional Count at NUTS-2 level

Post by EPO / PATSTAT Support » Wed May 12, 2021 10:45 am

Here is a (non-fractional count) example using NUTS-3 level (assigned to the patent applicant(s) on the EP-B1 publication), for EP applications that were granted between 2005 and 2019.
In the event of more then 1 applicant in different NUTS regions, each region will get "a count".
If you want this information for other countries, simply change IT in the " and tls206_person.nuts like 'IT%' " phrase to another country code.

Code: Select all

SELECT nuts.nuts ,   nuts.nuts_label, nuts.nuts_level,
COUNT(distinct(case when year(publn_date)  = 2005 then tls211_pat_publn.appln_id end)) as "2005",
COUNT(distinct(case when year(publn_date)  = 2006 then tls211_pat_publn.appln_id end)) as "2006",
COUNT(distinct(case when year(publn_date)  = 2007 then tls211_pat_publn.appln_id end)) as "2007",
COUNT(distinct(case when year(publn_date)  = 2008 then tls211_pat_publn.appln_id end)) as "2008",
COUNT(distinct(case when year(publn_date)  = 2009 then tls211_pat_publn.appln_id end)) as "2009",
COUNT(distinct(case when year(publn_date)  = 2010 then tls211_pat_publn.appln_id end)) as "2010",
COUNT(distinct(case when year(publn_date)  = 2011 then tls211_pat_publn.appln_id end)) as "2011",
COUNT(distinct(case when year(publn_date)  = 2012 then tls211_pat_publn.appln_id end)) as "2012",
COUNT(distinct(case when year(publn_date)  = 2013 then tls211_pat_publn.appln_id end)) as "2013",
COUNT(distinct(case when year(publn_date)  = 2014 then tls211_pat_publn.appln_id end)) as "2014",
COUNT(distinct(case when year(publn_date)  = 2015 then tls211_pat_publn.appln_id end)) as "2015",
COUNT(distinct(case when year(publn_date)  = 2016 then tls211_pat_publn.appln_id end)) as "2016",
COUNT(distinct(case when year(publn_date)  = 2017 then tls211_pat_publn.appln_id end)) as "2017",
COUNT(distinct(case when year(publn_date)  = 2018 then tls211_pat_publn.appln_id end)) as "2018",
COUNT(distinct(case when year(publn_date)  = 2019 then tls211_pat_publn.appln_id end)) as "2019"
FROM tls211_pat_publn join tls227_pers_publn on tls211_pat_publn.pat_publn_id = tls227_pers_publn.pat_publn_id
  join tls206_person  on tls206_person.person_id = tls227_pers_publn.person_id
  join (select nuts, nuts_label, nuts_level from tls904_nuts) nuts on nuts.nuts = tls206_person.nuts
    where publn_auth = 'ep'  and publn_kind = 'B1'
	and applt_seq_nr > 0 
	and year(publn_date)  between 2005 and 2019
	and tls206_person.nuts like 'IT%'
group by  nuts.nuts ,   nuts.nuts_label,nuts.nuts_level
order by   nuts.nuts
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply