With PATSTAT, there are endless possibilities to specify the "conditions" for those applicants (or patent applications).
Here are 5 variations on the same theme that have all been tested on PATSTAT Online.
Researchers can obtain a 1 month free test account on PATSTAT Online to get acquainted with the data and create their own search methodology.
1) A SQL query that creates a table containing the top 1000 applicants based on granted EP patents that were filed between 2010 and 2020.
Code: Select all
SELECT top 1000 psn_name,
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_id) total
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
WHERE
appln_auth = 'EP'
AND appln_filing_year between 2010 and 2020
and applt_seq_nr > 0
and granted = 'y' --remove this line if you want to have the number of patents filed instead of only granted patents
group by psn_name
order by total desc
Code: Select all
SELECT top 1000 psn_name,
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_id) total
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
WHERE
appln_auth = 'EP'
AND appln_filing_year between 2010 and 2020
and applt_seq_nr > 0
and granted = 'y' --remove this line if you want to have the number of patents filed instead of patents granted
and eu_member = 'Y'
group by psn_name
order by total desc
https://worldwide.espacenet.com/patent/ ... #!/CPC=A61
Code: Select all
SELECT top 1000 psn_name,
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_id) total
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
WHERE
appln_auth = 'EP'
AND appln_filing_year between 2010 and 2020
and applt_seq_nr > 0
and granted = 'y' --remove this line if you want to have the number of patents filed instead of only granted patents
and eu_member = 'Y'
and ((tls201_appln.appln_id in (select distinct appln_id from tls209_appln_ipc where left(ipc_class_symbol,3) = 'A61'))
or (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc where left(cpc_class_symbol,3) = 'A61')))
group by psn_name
order by total desc
Code: Select all
SELECT top 1000 psn_name,
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_id) total
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
WHERE
appln_auth = 'EP'
AND appln_filing_year between 2010 and 2020
and applt_seq_nr > 0
and granted = 'y' --remove this line if you want to have the number of patents filed instead of only granted patents
and eu_member = 'Y'
and ((tls201_appln.appln_id in (select distinct appln_id from tls209_appln_ipc where left(ipc_class_symbol,3) = 'A61'))
or (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc where left(cpc_class_symbol,3) = 'A61')))
and tls201_appln.appln_id in (select appln_id from tls231_inpadoc_legal_event where event_code = 'pgfp' and fee_country in ('FR','DE','IT','GB'))
group by psn_name
order by total desc
Code: Select all
SELECT top 1000 psn_name,
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_id) total
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
WHERE
appln_auth = 'EP'
AND appln_filing_year between 2010 and 2020
and applt_seq_nr > 0
and granted = 'y' --remove this line if you want to have the number of patents filed instead of only granted patents
and eu_member = 'Y'
and ((tls201_appln.appln_id in (select distinct appln_id from tls209_appln_ipc where left(ipc_class_symbol,3) = 'A61'))
or (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc where left(cpc_class_symbol,3) = 'A61')))
and tls201_appln.appln_id in (select appln_id from tls231_inpadoc_legal_event where event_code = 'pgfp' and fee_country in ('FR','DE','IT','GB'))
and tls201_appln.appln_id in (select appln_id from reg101_appln join reg130_opponent on reg101_appln.id = reg130_opponent.id)
group by psn_name
order by total desc
Code: Select all
SELECT tls201_appln.appln_id
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
WHERE
appln_auth = 'EP'
AND appln_filing_year between 2010 and 2020
and applt_seq_nr > 0
and granted = 'y'
and eu_member = 'Y'
and ((tls201_appln.appln_id in (select distinct appln_id from tls209_appln_ipc where left(ipc_class_symbol,3) = 'A61'))
or (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc where left(cpc_class_symbol,3) = 'A61')))
and tls201_appln.appln_id in (select appln_id from tls231_inpadoc_legal_event where event_code = 'pgfp' and fee_country in ('FR','DE','IT','GB'))
and tls201_appln.appln_id in (select appln_id from reg101_appln join reg130_opponent on reg101_appln.id = reg130_opponent.id)
group by tls201_appln.appln_id
Users can easily adapt the technical scope by changing the the conditions in the WHERE clause of the SQL query. One can also add (or remove) tables depending on your data needs. The PATSTAT Data Catalog gives a comprehensive overview of all the tables and attributes in PATSTAT. Other useful examples: