SQL - Applicant Country - IPC filter - NUTS Region

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

Ambereerland
Posts: 2
Joined: Thu Sep 15, 2022 11:00 am

SQL - Applicant Country - IPC filter - NUTS Region

Post by Ambereerland » Thu Nov 24, 2022 3:15 pm

Dear all,
For my Master’s thesis I am studying the effects of the immigration of high-skilled workers on the innovation of high-tech clusters. Therefore, I would like to have data on the number of high-tech patent applications (IPC) per NUTS-3 region (all countries in EU) per year per inventor’s country of residence. This is because some patents are filed in a certain country meanwhile the invention is done in a different country. In addition, I would like to know the nationality of the inventors (because of immigration effects). The period would be from 1990-2021. To illustrate what I mean, I have added a table below.

I had already started on a SQL query, but I am not sure how to progress. Any help is much appreciated!!
Kind regards, Koen

Code: Select all

SELECT a.appln_filing_year, COUNT(a.appln_id) AS NumberOfPatentApplications
FROM tls201_appln a
JOIN tls209_appln_ipc i ON a.appln_id = i.appln_id
JOIN tls207_pers_appln p_a ON p_a.appln_id = a.appln_id
JOIN tls206_person p ON p.person_id = p_a.person_id
JOIN tls904_nuts n on n.nuts = p.nuts
WHERE i.ipc_class_symbol in ('B41J', 'G06C', 'G06D', 'G06E', 'G11C   29/54', 'G06Q   10/00', 'G06Q   30-99/00', 'G06Q   20/00', 'G06G', 'G06J', 'G06F   3/01', 'G06M', 'B64B', 'B64C', 'B64D', 'B64F', 'B64G', 'C40B   10/00', 'C40B   40/00', 'C40B   50/18', 'C12P', 'C12Q', 'H01S', 'H01L', 'H04B', 'H04H', 'H04J', 'H04K', 'H04L', 'H04M', 'H04N', 'H04Q', 'H04R', 'H04S') 
WHERE n.nuts in ('NL', 'EE', 'BE', 'FR', 'ES', 'PT', 'UK', 'IE', 'IS', 'NO', 'SE', 'FI', 'LV', 'LT', 'PL', 'DE', 'LU', 'CH', 'AT', 'IT', 'CZ', 'SK', 'HU', 'SI', 'HR', 'RO', 'RS', 'BG', 'ME', 'MK', 'AL', 'EL', 'TR', 'CY', 'LI', 'MT') [/color]
AND person_ctry_code = 'DE'  -- applicant must be from country X [/color]
AND a.appln_filing_year BETWEEN 1990 AND 2021 -- Define year range here
GROUP BY a.appln_filing_year
ORDER BY a.appln_filing_year


Right now, the first red part is wrong and the second red part only lets me focus on one specific country.


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

Re: SQL - Applicant Country - IPC filter - NUTS Region

Post by EPO / PATSTAT Support » Mon Nov 28, 2022 4:07 pm

Hello Koen,
there are a couple of things you have to keep in mind;
the person_ctry_code is not the nationality, but the "place of business" (or the place of residence).
So a German inventor (nationality) working for a US company in Seattle, living in Seattle will have "US" as person country code.
A German inventor (nationality), working for a US Company, but "homeworking" in Germany will have "DE". Assuming that the applicant (the US company) has registered the inventor with his real address.
Some companies have a policy of systematically providing the companies address (and country) for all their inventors. (for example, in order to shield them from being contacted directly).
NUTS codes are based on the addresses in PATSTAT defined as "place of business".
Sometimes researchers use the country where a priority patent was filed as "the country where the invention was done". I have adapted your SQL, but you might want to re-visit your data collection methodology.

Code: Select all

SELECT a.appln_filing_year, p.nuts, nuts_label, COUNT(a.appln_id) AS NumberOfPatentApplications
FROM tls201_appln a
JOIN tls207_pers_appln p_a on a.appln_id = p_a.appln_id
JOIN tls206_person p ON p.person_id = p_a.person_id
JOIN tls209_appln_ipc i ON a.appln_id = i.appln_id
JOIN tls904_nuts n on n.nuts = p.nuts

WHERE left(i.ipc_class_symbol,4) in ('B41J', 'G06C', 'G06D', 'G06E', 'G11C 29/54', 'G06Q 10/00', 'G06Q 30-99/00', 
'G06Q 20/00', 'G06G', 'G06J', 'G06F 3/01', 'G06M', 'B64B', 'B64C', 'B64D', 'B64F', 'B64G', 'C40B 10/00', 
'C40B 40/00', 'C40B 50/18', 'C12P', 'C12Q', 'H01S', 'H01L', 'H04B', 'H04H', 'H04J', 'H04K', 'H04L', 'H04M', 
'H04N', 'H04Q', 'H04R', 'H04S')
AND left (n.nuts,2)  in ('NL', 'EE', 'BE', 'FR', 'ES', 'PT', 'UK', 'IE', 'IS', 'NO', 'SE', 'FI', 'LV', 'LT',
'PL', 'DE', 'LU', 'CH', 'AT', 'IT', 'CZ', 'SK', 'HU', 'SI', 'HR', 'RO', 'RS', 'BG', 'ME', 'MK', 'AL', 'EL', 'TR', 
'CY', 'LI', 'MT')
AND invt_seq_nr > 0  -- only INVENTORS
AND a.appln_filing_year BETWEEN 1990 AND 2021 -- Define year range here
GROUP BY a.appln_filing_year, p.nuts, nuts_label
ORDER BY a.appln_filing_year, p.nuts, nuts_label
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply