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.