Hello khanhhienbi,
first a couple of observations:
Patents that have been filed between '2016-01-01' and '2020-01-01' will mostly not be granted yet. It easily takes 3 to 4 years for a patent to become granted. So I have widened the scope, but you can easily adapt it to your needs.
If you want to "count" the number of patents, there is no need to JOIN the title of the application. So I have removed that table from the query.
And if you want to produce some kind of applicant ranking table, then you need to JOIN the tls206_person table in order to have the applicant names available for your ranking.
On the PATSTAT webpage, there are some publications with examples and sample queries to get started.
And also this forum has plenty of working sql queries that have been published to help researchers.
Code: Select all
SELECT psn_name,
COUNT(distinct(case when t1.appln_filing_year = 2010 then t1.appln_id end)) as '2010',
COUNT(distinct(case when t1.appln_filing_year = 2011 then t1.appln_id end)) as '2011',
COUNT(distinct(case when t1.appln_filing_year = 2012 then t1.appln_id end)) as '2012',
COUNT(distinct(case when t1.appln_filing_year = 2013 then t1.appln_id end)) as '2013',
COUNT(distinct(case when t1.appln_filing_year = 2014 then t1.appln_id end)) as '2014',
COUNT(distinct(case when t1.appln_filing_year = 2015 then t1.appln_id end)) as '2015',
COUNT(distinct(case when t1.appln_filing_year = 2016 then t1.appln_id end)) as '2016',
COUNT(distinct(case when t1.appln_filing_year = 2017 then t1.appln_id end)) as '2017',
COUNT(distinct(case when t1.appln_filing_year = 2018 then t1.appln_id end)) as '2018',
COUNT(distinct(case when t1.appln_filing_year = 2019 then t1.appln_id end)) as '2019',
count(t1.appln_id) total
FROM tls201_appln t1 JOIN tls209_appln_ipc t2
ON t1.appln_id = t2.appln_id
join tls207_pers_appln on t1.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
WHERE
t2.ipc_class_symbol LIKE 'C12N%'
AND t1.appln_auth IN ('GB')
AND t1.appln_filing_year between 2010 and 2019
and applt_seq_nr > 0 and invt_seq_nr = 0
and granted = 'y'
group by psn_name
order by total desc
Best regards,
Geert Boedt