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:
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.