Patents Application and Patents Granted per year (firm level)

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

studentaccount
Posts: 2
Joined: Wed Apr 18, 2018 11:58 am

Patents Application and Patents Granted per year (firm level)

Post by studentaccount » Wed Apr 18, 2018 1:03 pm

Dear community
I’m doing a research paper on innovation and are trying to retrieve patent data from PATSTAT, but I struggle a bit with the SQL coding and hope I can find some help here.

I’m looking at 250’ish European companies. E.g. “SYMRISE AG”, “DANONE”, “MONDI PLC”.

I need two things:

1) Patents granted, count per year
I need a query that returns the number of granted patents each year for a specific company. Looking at maximum time period. I’m fine with running the same query 250’ish times. Ideally, I would like the output to look like this:

Year Number of patents
1995 121
1996 134

2) Patents applications, count per year
Realizing the application processes has various speeds, I would like a similar query, but with the application dates instead of the patent granted dates.

Year Number of patent applications
1995 211
1996 348

PS: I understand that it might be good to group the patents into Patent families, and very much welcome this incorporated. Also, perhaps Earliest Filling date is the best suited variable for the second query (patens application per year), and also very much welcome this incorporated.

Thank you so much in advance
Kind regards
Julia


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

Re: Patents Application and Patents Granted per year (firm level)

Post by EPO / PATSTAT Support » Tue Apr 24, 2018 11:46 am

Hello Julia,
a count can be fairly easily done with a query similar to the one below.

Code: Select all

SELECT psn_name,
count(distinct((case when appln_filing_year < 1995 then tls201_appln.appln_id end))) as "<1995",
count(distinct((case when appln_filing_year = 1995 then tls201_appln.appln_id end))) as "1995",
count(distinct((case when appln_filing_year = 1996 then tls201_appln.appln_id end))) as "1996",
count(distinct((case when appln_filing_year = 1997 then tls201_appln.appln_id end))) as "1997",
count(distinct((case when appln_filing_year = 1998 then tls201_appln.appln_id end))) as "1998",
count(distinct((case when appln_filing_year = 1999 then tls201_appln.appln_id end))) as "1999",
count(distinct((case when appln_filing_year = 2000 then tls201_appln.appln_id end))) as "2000",
count(distinct((case when appln_filing_year = 2001 then tls201_appln.appln_id end))) as "2001",
count(distinct((case when appln_filing_year = 2002 then tls201_appln.appln_id end))) as "2002",
count(distinct((case when appln_filing_year = 2003 then tls201_appln.appln_id end))) as "2003",
count(distinct((case when appln_filing_year = 2004 then tls201_appln.appln_id end))) as "2004",
count(distinct((case when appln_filing_year = 2005 then tls201_appln.appln_id end))) as "2005",
count(distinct((case when appln_filing_year = 2006 then tls201_appln.appln_id end))) as "2006",
count(distinct((case when appln_filing_year = 2007 then tls201_appln.appln_id end))) as "2007",
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((case when appln_filing_year = 2015 then tls201_appln.appln_id end))) as "2015",
count(distinct((case when appln_filing_year = 2016 then tls201_appln.appln_id end))) as "2016",
count(distinct((case when appln_filing_year = 2017 then tls201_appln.appln_id end))) as "2017",
count(distinct(tls201_appln.appln_id )) as "total"
FROM tls201_appln 
  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
  join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
where 
(psn_name like '%DANONE%' or psn_name like '%SYMRISE%')
and applt_seq_nr > 0 and invt_seq_nr = 0
group by psn_name
order by psn_name
When you run the above query, you will observe that , even by using the PSN name, still quite some noise enters the results. You will need to adapt the WHERE clause in the SQL query to further limit (or extend), or simply copy the data to an excel sheet and delete the rows which you don't need.
In the above query, I used the % wildcard to extend the scope of the names, but you can just as well make a limited list of the names you want to have included.
As long as the name of the applicant is rather unique, the wild card approach will not introduce all too much noise.
The situation is different when you would use the same approach for Mondi.
Using "psn_name like '%MONDI%'" will include names such as MONDIAL, MONDINI,... and those are definitely noise. So in this case, you might want to use a more restrictive approach.

When I need to work with applicant names which I am not familiar with, I always run the query below to get a feeling what names I should be working with. It gives some good guidance on how to adapt the query to only include the names you really want to be part of the aggregation.

Code: Select all

SELECT  person_name,person_ctry_code,psn_id 
      , psn_name, psn_sector
	  ,count(tls207_pers_appln.appln_id) total
FROM  tls206_person 
  join tls207_pers_appln on tls206_person.person_id = tls207_pers_appln.person_id
where psn_name like '%MONDI%' 
	and applt_seq_nr > 0 
	and invt_seq_nr = 0
  group by  person_name, person_ctry_code, psn_id, psn_name, psn_sector 
order by total desc
To only count granted patents: add "granted = 1" in the WHERE clause.
To use other dates, join publication table or use any of the other dates.
If you want to avoid double counting inventions, then you should count the number of docdb families.
In the CASE clauses :change tls201_appln.appln_id by tls201_appln.docdb_family_id.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


studentaccount
Posts: 2
Joined: Wed Apr 18, 2018 11:58 am

Re: Patents Application and Patents Granted per year (firm level)

Post by studentaccount » Wed Apr 25, 2018 2:30 pm

Hey Support, thank you so much for your kind reply from which I was able to generate the code below, which yields a count of yearly patent applications and patents granted.

Code: Select all

SELECT psn_name,
count(distinct((case when appln_filing_year < 1995 then tls201_appln.docdb_family_id end))) as "<1995_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "<1995_Granted",
count(distinct((case when appln_filing_year = 1995 then tls201_appln.docdb_family_id end))) as "1995_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "1995_Granted",
count(distinct((case when appln_filing_year = 1996 then tls201_appln.docdb_family_id end))) as "1996_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "1996_Granted",
count(distinct((case when appln_filing_year = 1997 then tls201_appln.docdb_family_id end))) as "1997_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "1997_Granted",
count(distinct((case when appln_filing_year = 1998 then tls201_appln.docdb_family_id end))) as "1998_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "1998_Granted",
count(distinct((case when appln_filing_year = 1999 then tls201_appln.docdb_family_id end))) as "1999_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "1999_Granted",
count(distinct((case when appln_filing_year = 2000 then tls201_appln.docdb_family_id end))) as "2000_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2000_Granted",
count(distinct((case when appln_filing_year = 2001 then tls201_appln.docdb_family_id end))) as "2001_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2001_Granted",
count(distinct((case when appln_filing_year = 2002 then tls201_appln.docdb_family_id end))) as "2002_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2002_Granted",
count(distinct((case when appln_filing_year = 2003 then tls201_appln.docdb_family_id end))) as "2003_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2003_Granted",
count(distinct((case when appln_filing_year = 2004 then tls201_appln.docdb_family_id end))) as "2004_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2004_Granted",
count(distinct((case when appln_filing_year = 2005 then tls201_appln.docdb_family_id end))) as "2005_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2005_Granted",
count(distinct((case when appln_filing_year = 2006 then tls201_appln.docdb_family_id end))) as "2006_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2006_Granted",
count(distinct((case when appln_filing_year = 2007 then tls201_appln.docdb_family_id end))) as "2007_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2007_Granted",
count(distinct((case when appln_filing_year = 2008 then tls201_appln.docdb_family_id end))) as "2008_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2008_Granted",
count(distinct((case when appln_filing_year = 2009 then tls201_appln.docdb_family_id end))) as "2009_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2009_Granted",
count(distinct((case when appln_filing_year = 2010 then tls201_appln.docdb_family_id end))) as "2010_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2010_Granted",
count(distinct((case when appln_filing_year = 2011 then tls201_appln.docdb_family_id end))) as "2011_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2011_Granted",
count(distinct((case when appln_filing_year = 2012 then tls201_appln.docdb_family_id end))) as "2012_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2012_Granted",
count(distinct((case when appln_filing_year = 2013 then tls201_appln.docdb_family_id end))) as "2013_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2013_Granted",
count(distinct((case when appln_filing_year = 2014 then tls201_appln.docdb_family_id end))) as "2014_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2014_Granted",
count(distinct((case when appln_filing_year = 2015 then tls201_appln.docdb_family_id end))) as "2015_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2015_Granted",
count(distinct((case when appln_filing_year = 2016 then tls201_appln.docdb_family_id end))) as "2016_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2016_Granted",
count(distinct((case when appln_filing_year = 2017 then tls201_appln.docdb_family_id end))) as "2017_Applications",
count(distinct(case when appln_filing_year < 1995 and granted = 1 then tls201_appln.docdb_family_id end)) as "2017_Granted", 
count(distinct(tls201_appln.docdb_family_id)) as "total"
FROM tls201_appln 
  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
  join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
where 
(psn_name like '%DANONE%')
and applt_seq_nr > 0 and invt_seq_nr = 0
group by psn_name
order by psn_name
The formatting is not perfect, but nothing that cannot be fixed in Excel. Ideally the output would be sorted in first “applications” year 1,2,3, etc, and then “granted” year 1,2,3, etc., but you can’t have it all :)

Once again thanks. I posted my code, maybe somebody can use in future. xxx


Post Reply