Help with SQL query - How to merge patent's applicants table with number of patent applications table

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

khanhhienbi
Posts: 13
Joined: Thu Sep 03, 2020 9:49 am

Help with SQL query - How to merge patent's applicants table with number of patent applications table

Post by khanhhienbi » Tue Nov 30, 2021 11:17 am

Hi everyone,

I am currently working with PATSTAT Autumn 2021 edition. I would like to retrieve a sample of the number of patent applications per year filed by UK biotechnology and pharmaceutical firms from 2011 to 2021.

This is my code to retrieve the patent's applicants details:

Code: Select all

SELECT app.appln_id, app.appln_auth, app.appln_nr, app.appln_filing_date, 
STRING_AGG ((person.psn_name), ', ') applicants, apptitle.appln_title, avg(techn.techn_field_nr) techn_nr
FROM
 tls201_appln app
 join tls207_pers_appln personapp on personapp.appln_id = app.appln_id
 join tls206_person person on personapp.person_id = person.person_id
 join tls230_appln_techn_field techn on techn.appln_id = app.appln_id
 left JOIN tls202_appln_title apptitle ON apptitle.appln_id = app.appln_id
WHERE
techn.techn_field_nr in (14,15)
AND app.appln_auth IN ('GB')
AND person.person_ctry_code = 'GB'
AND personapp.applt_seq_nr > 0 and personapp.invt_seq_nr = 0
AND app.appln_filing_date between '2011-01-01' and '2019-01-01'
group by app.appln_id, app.appln_auth, app.appln_nr, app.appln_filing_date ,apptitle.appln_title
order by app.appln_filing_date, app.appln_id
This is my code to get the number of patent applications per year:

Code: Select all

SELECT psn_name, 
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(distinct(case when t1.appln_filing_year = 2019 then t1.appln_id end)) as '2020',
COUNT(distinct(case when t1.appln_filing_year = 2019 then t1.appln_id end)) as '2021'
FROM   tls201_appln t1 JOIN  tls230_appln_techn_field on t1.appln_id = tls230_appln_techn_field.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
tls230_appln_techn_field.techn_field_nr = 15 --> number used for techn_field = 'Biotechnology'
AND t1.appln_filing_year between 2011 and 2019
and applt_seq_nr > 0 and invt_seq_nr = 0--and psn_sector <> 'individual'
and appln_auth = 'GB' 
and person_ctry_code = 'GB'
group by psn_name
Could you please check if my codes are correct? And how to merge these two tables together?

Also, is there any way for researchers to identify which company is venture-backed or non-venture-backed?

Any help is much appreciated! Many thanks!


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Help with SQL query - How to merge patent's applicants table with number of patent applications table

Post by EPO / PATSTAT Support » Tue Nov 30, 2021 8:27 pm

Hello khanhhienbi,
the two tables are very different, and it is not clear how you would want them "combined". The first one is a list of patents, the second one an aggregated table on total number of filings per year. How would a combined table look like ?
About the queries, not sure why you used an AVG function, but if the purpose is to have a flag that marks the patents that are assigned to both technical areas, then you will need to be sure that the average value (14.5) can be represented in 1 field. That would be something like below: (it's easier to take the sum instead of avg --> 29)

Code: Select all

SELECT app.appln_id, app.appln_auth, app.appln_nr, app.appln_filing_date, 
STRING_AGG ((person.psn_name), ', ') applicants, apptitle.appln_title,  CAST((AVG(1.00*techn_field_nr)) AS DEC(10,2)) techn_nr
FROM
 tls201_appln app
 join tls207_pers_appln personapp on personapp.appln_id = app.appln_id
 join tls206_person person on personapp.person_id = person.person_id
 join tls230_appln_techn_field techn on techn.appln_id = app.appln_id
 left JOIN tls202_appln_title apptitle ON apptitle.appln_id = app.appln_id
WHERE
techn.techn_field_nr in (14,15)
AND app.appln_auth IN ('GB')
AND person.person_ctry_code = 'GB'
AND personapp.applt_seq_nr > 0 and personapp.invt_seq_nr = 0
AND app.appln_filing_date between '2011-01-01' and '2019-01-01'
and app.appln_id = 446486636
group by app.appln_id, app.appln_auth, app.appln_nr, app.appln_filing_date, 
 apptitle.appln_title
order by app.appln_filing_date, app.appln_id
On the second query, you missed out changing a couple of the years in the last CASE statements.
And if you add a TOTAL line, then you can easily do a ranking.
From a methodological point of view: you have to keep in mind that patent filed by GB persons can just as well be filed via the EPO, or as PCT's; and consequently provide protection in GB.. Whether you include them (via a PCT receiving office or EP as appln_auth condition) depends on what data exactly you need.
There are a couple of different "routes" an applicant can follow to finally obtain protection in GB - or/and - other countries. Observe how the totals completely change. It also good practice to count patent families instead of applications - to avoid double counting when combining different filing routes.

Code: Select all

SELECT psn_name, 
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(distinct(case when t1.appln_filing_year = 2020 then t1.appln_id end)) as '2020',
COUNT(distinct(case when t1.appln_filing_year = 2021 then t1.appln_id end)) as '2021',
COUNT(distinct(case when t1.appln_filing_year between 2011 and 2021 then t1.appln_id end)) as 'total'
FROM   tls201_appln t1 JOIN  tls230_appln_techn_field on t1.appln_id = tls230_appln_techn_field.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
tls230_appln_techn_field.techn_field_nr = 15 --> number used for techn_field = 'Biotechnology'
AND t1.appln_filing_year between 2011 and 2019
and applt_seq_nr > 0 and invt_seq_nr = 0--and psn_sector <> 'individual'
--and (appln_auth = 'GB' ) 
and (appln_auth = 'GB' or receiving_office = 'GB') 
and person_ctry_code = 'GB'
group by psn_name
order by total desc
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


khanhhienbi
Posts: 13
Joined: Thu Sep 03, 2020 9:49 am

Re: Help with SQL query - How to merge patent's applicants table with number of patent applications table

Post by khanhhienbi » Wed Dec 01, 2021 11:44 am

Dear EPO/ PATSTAT support,

Thank you very much for your prompt reply and constructive codes. They're really helpful for my research!

I mistakenly acknowledge between the key bibliographical data elements of the patent application as for the patent applicants.

Many thanks again!


Post Reply