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
```