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