Count Patent applications that are granted and forward citation in specific year for specific 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

562698tb
Posts: 2
Joined: Tue Mar 07, 2023 9:01 am

Count Patent applications that are granted and forward citation in specific year for specific company

Post by 562698tb » Wed Mar 08, 2023 6:16 pm

Hi,

When using this query:

Code: Select all

SELECT psn_name, 
COUNT(distinct(case when  appln_filing_year = 2008 then tls201_appln.appln_id end)) as '2008',
COUNT(distinct(case when  appln_filing_year = 2009 then tls201_appln.appln_id end)) as '2009',
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when  appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when  appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when  appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when  appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
count (distinct (tls201_appln.appln_id)) total
FROM   tls201_appln  
join tls211_pat_publn on tls201_appln.earliest_pat_publn_id = tls211_pat_publn.pat_publn_id
join tls227_pers_publn on tls211_pat_publn.pat_publn_id = tls227_pers_publn.pat_publn_id
join tls206_person on tls227_pers_publn.person_id = tls206_person.person_id
WHERE
appln_auth = 'EP'
and applt_seq_nr > 0 
and granted = 'y' --remove this line if you want to have the number of patents filed instead of only granted patents 
and (psn_name = 'xx')
group by psn_name
order by total desc
What does : " count (distinct (tls201_appln.appln_id)) total " mean? Does it gives me the total number of granted patent for a specific company?

In addition,

Code: Select all

SELECT psn_name,
sum(case when appln_filing_year = 1980 then a.nb_citing_docdb_fam end) as "1980",
sum(case when appln_filing_year = 1981 then a.nb_citing_docdb_fam end) as "1981",
sum(case when appln_filing_year = 1982 then a.nb_citing_docdb_fam end) as "1982",
sum(case when appln_filing_year = 1983 then a.nb_citing_docdb_fam end) as "1983",
sum(case when appln_filing_year = 1984 then a.nb_citing_docdb_fam end) as "1984",
sum(case when appln_filing_year = 1985 then a.nb_citing_docdb_fam end) as "1985",
sum(case when appln_filing_year = 1986 then a.nb_citing_docdb_fam end) as "1986",
sum(case when appln_filing_year = 1987 then a.nb_citing_docdb_fam end) as "1987",
sum(case when appln_filing_year = 1988 then a.nb_citing_docdb_fam end) as "1988",
sum(case when appln_filing_year = 1989 then a.nb_citing_docdb_fam end) as "1989",
sum (a.nb_citing_docdb_fam) total
FROM tls201_appln a
join tls207_pers_appln b on a.appln_id = b.appln_id
join tls206_person c on b.person_id = c.person_id
join tls801_country on c.person_ctry_code= tls801_country.ctry_code
WHERE
appln_filing_year between 1980 and 1989
and granted = 'Y'
and eu_member = 'Y'
and applt_seq_nr > 0 
and nb_citing_docdb_fam > 0
and psn_sector = 'company'
and (psn_name = 'astra' or psn_name = 'zeneca' or psn_name = 'astrazeneca')
group by psn_name
ORDER BY psn_name
What does the output actually entails? Does it give me the forward citations from granted patents in that specific year?

Thank you in advance.


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

Re: Count Patent applications that are granted and forward citation in specific year for specific company

Post by EPO / PATSTAT Support » Thu Mar 09, 2023 10:00 am

a COUNT( DISTINCT (tls201_appln.appln_id)) guarantees that each application is counted only one time. For this query, it does not make much difference, but in PATSTAT it is good practice to use DISTINCT to avoid unwanted double counting of patent applications.
The query gives you the total number of granted patents that have been filed at the EPO for a specific company.

The second query gives you a total number (sum) of forward citations for each of the 3 companies,
aggregated on the filing year of the respective applications. On as side note: the query is a bit flawed from a methodological point of view because the attribute nb_citing_docdb_fam is family based, and the sum is based on the filing year of the application. So families having multiple applications in different years will be added for each of the years, which is a form of double counting. The query was originally meant to illustrate a "before and after" of the Astra Zeneca merger, where the "real figures" are less important then the trend. Astra Zeneca merged in 1999, so this "shortened version" does not show what happened around 1999.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


562698tb
Posts: 2
Joined: Tue Mar 07, 2023 9:01 am

Re: Count Patent applications that are granted and forward citation in specific year for specific company

Post by 562698tb » Tue Mar 14, 2023 2:16 pm

Hi,

Thank you so much for the response and pointing out the methodology. Is there also a query to find DOCB Count data filled in a specific year and granted? Same as the first patent granted query above?

Thank you.


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

Re: Count Patent applications that are granted and forward citation in specific year for specific company

Post by EPO / PATSTAT Support » Thu Mar 30, 2023 4:09 pm

That is a bit tricky because a family can have multiple granted applications filed in different years. But one can take the earliest_filing_year and count the families. The below query will give you the totals based on the earliest application filing year for each patent family member keeping in mind that if a family has 2 applications that have different earliest_filing_years, they will be counted for each year.
The fact that one uses the "earliest_filing_year" will already group most family members in the same year.

Code: Select all

SELECT psn_name, 
COUNT(distinct(case when  earliest_filing_year = 2008 then tls201_appln.docdb_family_id end)) as '2008',
COUNT(distinct(case when  earliest_filing_year = 2009 then tls201_appln.docdb_family_id end)) as '2009',
COUNT(distinct(case when earliest_filing_year = 2010 then tls201_appln.docdb_family_id end)) as '2010',
COUNT(distinct(case when  earliest_filing_year = 2011 then tls201_appln.docdb_family_id end)) as '2011',
COUNT(distinct(case when  earliest_filing_year = 2012 then tls201_appln.docdb_family_id end)) as '2012',
COUNT(distinct(case when  earliest_filing_year = 2013 then tls201_appln.docdb_family_id end)) as '2013',
COUNT(distinct(case when  earliest_filing_year = 2014 then tls201_appln.docdb_family_id end)) as '2014',
COUNT(distinct(case when  earliest_filing_year = 2015 then tls201_appln.docdb_family_id end)) as '2015',
COUNT(distinct(case when  earliest_filing_year = 2016 then tls201_appln.docdb_family_id end)) as '2016',
COUNT(distinct(case when  earliest_filing_year = 2017 then tls201_appln.docdb_family_id end)) as '2017',
COUNT(distinct(case when  earliest_filing_year = 2018 then tls201_appln.docdb_family_id end)) as '2018',
COUNT(distinct(case when  earliest_filing_year = 2019 then tls201_appln.docdb_family_id end)) as '2019',
COUNT(distinct(case when  earliest_filing_year = 2020 then tls201_appln.docdb_family_id end)) as '2021',
COUNT(distinct(case when  earliest_filing_year = 2022 then tls201_appln.docdb_family_id end)) as '2022',
count (distinct (tls201_appln.docdb_family_id)) total
FROM   tls201_appln  
join tls211_pat_publn on tls201_appln.earliest_pat_publn_id = tls211_pat_publn.pat_publn_id
join tls227_pers_publn on tls211_pat_publn.pat_publn_id = tls227_pers_publn.pat_publn_id
join tls206_person on tls227_pers_publn.person_id = tls206_person.person_id
WHERE
appln_auth = 'EP'
and applt_seq_nr > 0 
and granted = 'y'
and psn_name = 'siemens'
group by psn_name
order by total desc
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply