How to count number of granted patents per company in a specific year

[sanobe]

Dear all,

I'm currently writing my master thesis, in which I'm analysing M&A deals in Europe and patents. I have a dataset of mergers and acquisitions between 2000 and 2021, and I need to know the number of granted patents that both the acquirer and target company had (not combined, but separately) at the time of the deal.

As I am new to PATSTAT and SQL, how can I design a suitable query for this? Any help would be highly appreciated.

Kind regards

[EPO / PATSTAT Support]

Hello Sanobe,
in the first post of the forum, there is an example that closely matches what you are looking for:
sql-queries-to-create-simple-applicant- ... able-10588

But if you want to better observe what happens before and after a merger, it would be advisable to use the applicant names that are specifically linked to the earliest publications of the patent application.
Applicants can request to have the name of the patent owners changed following a merger, and those will be the names linked to the applications when later publications (for example a granting) happened. So that will obscure a bit the real numbers before the merger.

Therefore, as a variation on the above forum post, here is a better query using "astrazeneca" as an example.

Code: Select all

SELECT psn_name, 
COUNT(distinct(case when appln_filing_year = 1990 then tls201_appln.appln_id end)) as '1990',
COUNT(distinct(case when  appln_filing_year = 1991 then tls201_appln.appln_id end)) as '1991',
COUNT(distinct(case when  appln_filing_year = 1992 then tls201_appln.appln_id end)) as '1992',
COUNT(distinct(case when  appln_filing_year = 1993 then tls201_appln.appln_id end)) as '1993',
COUNT(distinct(case when  appln_filing_year = 1994 then tls201_appln.appln_id end)) as '1994',
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(case when  appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when  appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when  appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
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 = 'astra' or psn_name = 'zeneca' or psn_name =  'astrazeneca')
group by psn_name
order by total desc

[sanobe]

Thank you for the reply and the link.

One more question, I just ran the code you provided; the table that I get from running the code shows the total amount of granted patents that a company owns in each year, correct? Because in that case, I believe I'm all set!

Thank you in advance!

[EPO / PATSTAT Support]

The table is based on the application filing year, and the name of the patent owner as currently registered in the EPO register.
That does not necessarily mean that the patent was already granted "at the time of the deal." When I look at the timeline, I would suspect that Astrazeneca executed ownership changes from Astra and Zeneca to Astrazeneca when the patent process was still ongoing. That would be logical and expected.
If you really want to use a fixed date, then you would need to use the date of the B1 publication (for EP patents), as that is the exact date the patent was granted. It can take 3-5 (or more) years for a patent to become granted. The A1 publication (for EP's) linked to tls206 via tls227 will give you the exact name of the company that filed the patent. The B1 publication will give you the name of the patent owner when the patent was granted. Later changes of ownership will be visible via linking tls206 and tls207 to the tls201 table. But I think it will make it rather complex to take all those variations into account. It all depends what you research question is about.

And if you look a bit more in detail at the set of possible harmonised names, you probably need to do some data cleaning to be sure to include all the variations of the entities.

Code: Select all

SELECT [psn_id]
      ,[psn_name]
      , 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 applt_seq_nr>0 
and   psn_name like  '%astra%zeneca%'
group by  [psn_id],[psn_name]
order by total desc

Cleaning and selecting will need to be done for Astra (and Zeneca) as well.
See:

Code: Select all

SELECT [psn_id]
      ,[psn_name]
      , 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 applt_seq_nr>0 
and   psn_name like  'astra %'
group by  [psn_id],[psn_name]
order by total desc