I would like to have some clarifications about the following query with which download patent data aggregated at regional level for European countries in selected IPC classes:
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 p.nuts = n.nuts and N.nuts_level <=3
WHERE i.ipc_class_symbol in ('B60L 7/00', 'B60L 7/02', 'B60L 7/04', 'B60L 7/06', 'B60L 7/08', 'B60L 7/10', 'B60L 7/12', 'B60L 7/14', 'B60L 7/16', 'B60L 7/18', 'B60L 7/20', 'B60L 7/22', 'B60L 7/24', 'B60L 7/26', 'B60L 7/28', 'B60L 50/00', 'B60L 50/10', 'B60L 50/11', 'B60L 50/12', 'B60L 50/13', 'B60L 50/14', 'B60L 50/15', 'B60L 50/16', 'B60L 50/20', 'B60L 50/30', 'B60L 50/40', 'B60L 50/50', 'B60L 50/51', 'B60L 50/52', 'B60L 50/53', 'B60L 50/60', 'B60L 50/61', 'B60L 50/62', 'B60L 50/64', 'B60L 50/70', 'B60L 50/71', 'B60L 50/72', 'B60L 50/75', 'B60L 50/90', 'B60L 53/00', 'B60L 53/10', 'B60L 53/12', 'B60L 53/122', 'B60L 53/124', 'B60L 53/126', 'B60L 53/14', 'B60L 53/16', 'B60L 53/18', 'B60L 53/20', 'B60L 53/22', 'B60L 53/24', 'B60L 53/30', 'B60L 53/302', 'B60L 53/31', 'B60L 53/34', 'B60L 53/35', 'B60L 53/36', 'B60L 53/37', 'B60L 53/38', 'B60L 53/39', 'B60L 53/50', 'B60L 53/51', 'B60L 53/52', 'B60L 53/53', 'B60L 53/54', 'B60L 53/55', 'B60L 53/56', 'B60L 53/57', 'B60L 53/60', 'B60L 53/62', 'B60L 53/63', 'B60L 53/64', 'B60L 53/65', 'B60L 53/66', 'B60L 53/67', 'B60L 53/68', 'B60L 53/80', 'B60L 55/00', 'B60L 58/00', 'B60L 58/10', 'B60L 58/12', 'B60L 58/13', 'B60L 58/14', 'B60L 58/15', 'B60L 58/16', 'B60L 58/18', 'B60L 58/19', 'B60L 58/20', 'B60L 58/21', 'B60L 58/22', 'B60L 58/24', 'B60L 58/25', 'B60L 58/26', 'B60L 58/27', 'B60L 58/30', 'B60L 58/31', 'B60L 58/32', 'B60L 58/33', 'B60L 58/34', 'B60L 58/40', 'B63B 1/00', 'B63B 1/02', 'B63B 1/04', 'B63B 1/06', 'B63B 1/08', 'B63B 1/10', 'B63B 1/12', 'B63B 1/14', 'B63B 1/16', 'B63B 1/18', 'B63B 1/20', 'B63B 1/22', 'B63B 1/24', 'B63B 1/26', 'B63B 1/28', 'B63B 1/30', 'B63B 1/32', 'B63B 1/34', 'B63B 1/36', 'B63B 1/38', 'B63B 1/40', 'C10L 5/40', 'C10L 5/42', 'C10L 5/44', 'C10L 5/46', 'C10L 5/48', 'C12P 7/00', 'C12P 7/02', 'C12P 7/04', 'C12P 7/06', 'C12P 7/08', 'C12P 7/10', 'C12P 7/12', 'C12P 7/14', 'C12P 7/16', 'C12P 7/18')
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 2010 AND 2022
GROUP BY a.appln_filing_year, p.nuts, nuts_label
ORDER BY a.appln_filing_year, p.nuts, nuts_label
But more important, given that I get also country aggregate data, I have noticed that the number of applications for a country is not equal to the sum of the number of applications of its provinces.
So, I would like to understand if there is a missing assignment of patents to EU regions and it is more conveniente for me download data at micro level.
Thank you in advance.