Patent count yearly per company

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

Rutgervtspijker
Posts: 3
Joined: Mon Apr 27, 2020 12:09 pm

Patent count yearly per company

Post by Rutgervtspijker » Mon Apr 27, 2020 12:15 pm

Hi all,

I am currently writing a research paper on GVC and its influence on patenting (innovation) before and after investment.

I found the following code on the forum:

Code: Select all

SELECT psn_name,
COUNT(distinct(case when year(appln_filing_date) = 2002 then tls201_appln.appln_id end)) as "2002",
COUNT(distinct(case when year(appln_filing_date) = 2003 then tls201_appln.appln_id end)) as "2003",
COUNT(distinct(case when year(appln_filing_date) = 2004 then tls201_appln.appln_id end)) as "2004",
COUNT(distinct(case when year(appln_filing_date) = 2005 then tls201_appln.appln_id end)) as "2005",
COUNT(distinct(case when year(appln_filing_date) = 2006 then tls201_appln.appln_id end)) as "2006",
COUNT(distinct(case when year(appln_filing_date) = 2007 then tls201_appln.appln_id end)) as "2007",
COUNT(distinct(case when year(appln_filing_date) = 2008 then tls201_appln.appln_id end)) as "2008",
COUNT(distinct(case when year(appln_filing_date) = 2009 then tls201_appln.appln_id end)) as "2009",
COUNT(distinct(case when year(appln_filing_date) = 2010 then tls201_appln.appln_id end)) as "2010",
COUNT(distinct(case when year(appln_filing_date) = 2011 then tls201_appln.appln_id end)) as "2011",
COUNT(distinct(case when year(appln_filing_date) = 2012 then tls201_appln.appln_id end)) as "2012",
COUNT(distinct(case when year(appln_filing_date) = 2013 then tls201_appln.appln_id end)) as "2013",
COUNT(distinct(case when year(appln_filing_date) = 2014 then tls201_appln.appln_id end)) as "2014",
COUNT(distinct(case when year(appln_filing_date) = 2015 then tls201_appln.appln_id end)) as "2015",
COUNT(distinct(case when year(appln_filing_date) = 2016 then tls201_appln.appln_id end)) as "2016",
COUNT(distinct(case when year(appln_filing_date) = 2017 then tls201_appln.appln_id end)) as "2017",
COUNT(distinct(case when year(appln_filing_date) = 2018 then tls201_appln.appln_id end)) as "2018",
COUNT(distinct(case when year(appln_filing_date) = 2019 then tls201_appln.appln_id end)) as "2019"
FROM tls201_appln
join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person  on tls206_person.person_id = tls207_pers_appln.person_id
where psn_name = 'Acal energy' and applt_seq_nr > 0  and appln_auth = 'EP' and appln_filing_year between 2002 and 2020
group by psn_name
order by psn_name
This code works fine and I can connect this to the investment date / year. However, I would like to add an weighted label to it, namely the 'patent value', this would be passive citations, how can I find this is this possible?

Thank you in advance


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

Re: Patent count yearly per company

Post by EPO / PATSTAT Support » Tue Apr 28, 2020 8:59 am

Hello Rutger,
how would you calculate that indicator. For each application year ?
Forward citations are available as a pre-aggregated attribute; one could make the sum and then normalise it over the number of applications - arriving at some kind of index that shows a "citation ratio".
Or do you have something else in mind ?
Geert Boedt
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Rutgervtspijker
Posts: 3
Joined: Mon Apr 27, 2020 12:09 pm

Re: Patent count yearly per company

Post by Rutgervtspijker » Tue Apr 28, 2020 4:58 pm

Dear geert,

Thank you for the reply. Yes I will illustrate an example for you e.g. spotify. Spotify receives an investment at time X then I would like to have the patent applications of spotify 5 years prior the investment and 5 years after. I will do this annually and for the patstat data just an timeframe on an annual basis from 2000 until 2020 will be ok. Then ideally I would like to have some kind of ratio that weights the application to citations. The code from the previous message gave me such a count return.

The variables I would ultimately use are ideally i) yearly patent counts ii) Citations/patent in subsequent years. The idea of this measure is that it captures the importance of the patents applied for.
I also want to relate to the most simple way of patent applications, so that there is data available up to the most recent days.

To sum up, the patent counts must be on an annual basis and on a company basis and ideally then it would be a ratio of citations/patents or vice versa.

Thank you,
Rutger


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

Re: Patent count yearly per company

Post by EPO / PATSTAT Support » Wed Apr 29, 2020 11:56 am

Here is a sample query that calculates a citation ratio.
The ratio makes use of the pre-aggregated attribute "tls201_appln.nb_citing_docdb_fam".
This attribute takes into account all citations to ALL family members of the EP applications.
(See data catalog:
DataCatalog_Global_v5.14.pdf
(2.63 MiB) Downloaded 32 times
Researchers will normally establish a time frame on the forward citations to avoid bias of the applications that have been around for a longer time and therefore have had more opportunities to be cited.
There is numerous literature on how to do that, but squeezing it into a single SQL query will become rather complex. For such analysis, I think the only solution is to have a local PATSTAT database (or extraction) so that intermediate temporary tables can be created an stored.
On the other hand, if the purpose of your research is to benchmark companies against each other over and time period (and not individual patents), I would think that bias is of no statistical importance as it will be across all companies.

Code: Select all

SELECT psn_name,

COUNT(distinct(case when year(appln_filing_date) = 2010 then tls201_appln.appln_id end)) as "2010",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2010 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2010 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2010",


COUNT(distinct(case when year(appln_filing_date) = 2011 then tls201_appln.appln_id end)) as "2011",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2011 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2011 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2011",


COUNT(distinct(case when year(appln_filing_date) = 2012 then tls201_appln.appln_id end)) as "2012",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2012 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2012 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2012",


COUNT(distinct(case when year(appln_filing_date) = 2013 then tls201_appln.appln_id end)) as "2013",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2013 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2013 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2013",

COUNT(distinct(case when year(appln_filing_date) = 2014 then tls201_appln.appln_id end)) as "2014",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2014 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2014 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2014",


COUNT(distinct(case when year(appln_filing_date) = 2015 then tls201_appln.appln_id end)) as "2015",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2015 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2015 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2015",

COUNT(distinct(case when year(appln_filing_date) = 2016 then tls201_appln.appln_id end)) as "2016",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2016 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2016 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2016",

COUNT(distinct(case when year(appln_filing_date) = 2017 then tls201_appln.appln_id end)) as "2017",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2017 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2017 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2017",

COUNT(distinct(case when year(appln_filing_date) = 2018 then tls201_appln.appln_id end)) as "2018",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2018 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2018 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2018",

COUNT(distinct(case when year(appln_filing_date) = 2019 then tls201_appln.appln_id end)) as "2019",
ROUND(CAST (1.0*sum ((case when year(appln_filing_date) = 2019 then tls201_appln.nb_citing_docdb_fam end)) /
	COUNT(distinct(case when year(appln_filing_date) = 2019 then tls201_appln.appln_id end))
	AS decimal (5,2)),2)  as "CR2019"

FROM tls201_appln
join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person  on tls206_person.person_id = tls207_pers_appln.person_id
where psn_name in ('SPOTIFY','MICROSOFT TECHNOLOGY LICENSING','MICROSOFT CORPORATION','HUAWEI TECHNOLOGIES COMPANY') 
and applt_seq_nr > 0  
and appln_auth = 'EP' 
and appln_filing_year between 2010 and 2019
group by psn_name
order by psn_name
I hope you find the information useful, the rest is up to you.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply