EPO - Discussion forums

Post your opinions, discuss, share information and knowledge about the European Patent Office's tools and services.
https://forums.epo.org/

Patent count yearly per company

https://forums.epo.org/viewtopic.php?t=9044

Page 1 of 1

Patent count yearly per company

Posted: Mon Apr 27, 2020 12:15 pm
by Rutgervtspijker
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

Re: Patent count yearly per company

Posted: Tue Apr 28, 2020 8:59 am
by EPO / PATSTAT Support
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

Re: Patent count yearly per company

Posted: Tue Apr 28, 2020 4:58 pm
by Rutgervtspijker
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

Re: Patent count yearly per company

Posted: Wed Apr 29, 2020 11:56 am
by EPO / PATSTAT Support
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 204 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.
All times are UTC+01:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/