Query by type of applicants

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

Isabel C.
Posts: 7
Joined: Sat Apr 10, 2021 9:31 am

Query by type of applicants

Post by Isabel C. » Sat Apr 10, 2021 3:05 pm

Dear PATSTAT community,

I am a second-year Ph.D. student in economics. I am currently working on a project on sophisticated network analysis on French collaborative patents.

I am interested in listing the patent applications that include more than one applicant, where one is a private company, and at least one is a public R&D entity (one university or CNRS). Moreover, I want to identify several sectors and fields: Electrical engineering (in particular, Telecommunications, Basic communication processes Semiconductors); Chemistry (Biotechnology, Pharmaceuticals and Microstructural and nano-technology) and Mechanical engineering (Machine tools, Engines, pumps, turbines, Mechanical elements). The period should comprehend 1978-2018. Last, if it is feasible, I would need the addresses to evaluate the geographical proximity.

Is it possible to do this query by “type” of applicants? If so, could you help me in extracting all this information?

Thank you very much for your help.


Posts: 262
Joined: Thu Feb 22, 2007 5:33 pm

Re: Query by type of applicants

Post by EPO / PATSTAT Support » Tue Apr 27, 2021 1:08 pm

Hello Isabel,
we had some out-of-forum exchanges on your request, so for the sake of the community, I replicate the main points here in the forum.
Applications that have more then 2 applicants can easily be identified in the tls201_appln table using the pre-agregated field and condition: nb_applicants > 1
If the 2 applicants have to fulfill specific conditions, then this can imbedded into small subqueries in the WHERE clause, forcing the application to belong to a "certain" sample. In this case: (at least) 1 applicant should be a company, and (at least) 1 should be a public R&D entity. When we analysed the data in the results, we observed that the assignement of the psn_sector data is not always consistent over time and therefore might need manual corrections by the researcher.
Technological fields and sectors can be filtered via the "technical field number" in the tls230_appln_techn_field table. Other solutions could be to use the IPC or CPC codes in order to narrow down to very specific areas. In the example I used techn_field_nr= 27 which covers: Mechanical engineering--> engines, pumps and turbines
To define "the period", researchers can use the various dates available such as application filing data, priority date, publication date, grant date,... This depends on the kind of analysis. In the example, I used "and appln_filing_year > 1977" . I also limited to EP applications to reduce the amount of retreived data.
You can adapt the query further to your needs.

Code: Select all

SELECT distinct tls201_appln.appln_id, appln_auth,appln_nr, appln_kind, appln_filing_date,
tls206_person.person_id, psn_name, person_ctry_code, psn_sector, appln_title
  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
  join tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
  left join tls202_appln_title on tls201_appln.appln_id = tls202_appln_title.appln_id
WHERE nb_applicants > 1
tls201_appln.appln_id in 
(select appln_id from tls207_pers_appln join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id where psn_sector in  
             'COMPANY GOV NON-PROFIT',
             'COMPANY HOSPITAL',
             'COMPANY UNIVERSITY')
             and applt_seq_nr > 0 )
tls201_appln.appln_id in 
(select appln_id from tls207_pers_appln join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id where psn_sector in  
              and applt_seq_nr > 0)
and applt_seq_nr > 0
and appln_filing_year > 1977
and appln_auth = 'EP'
and techn_field_nr = 27 --> the code used for Biotechnology
order by appln_filing_date,tls201_appln.appln_id
The "geographical" proximity of applicants is not covered in this query. That can become complex... assigning lat/lon coordinates and calculating the "real" distance. Or you use the NUTS codes which are available in the TLS206_person table, and simply check if they are the same. Even this could become rather complicated as there could be more then 2 applicants. NUTS codes only provide a certain level of granularity and do not contain data on what the distance is between 2 NUTS codes.
Eurostat has a data set that gives distances at NUTS2 level; could be an interesting combined data set with the patent person data... link
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org

Post Reply