Hello Lorens,
have a look here:
sql-queries-to-create-simple-applicant- ... able-10588
And in the PATSTAT Data Catalog you will find detailed information on the data base model and how the most used JOINS between the tables can be constructed.

I would only use the PGFP as a proxy. It under-estimates indeed a couple of countries in very specific situations. AT, GB do not charge renewal fees before year 4,5,6 which means that some applications that were quickly granted will stay under the radar using PGFP. (IT does not charge the renewal fe...

Hello schilma7, There are no "types of priorities" (with the small exceptions of utility models, design patents or plant patents -maybe there are some more...-). You linked the application to the publications and what you see are the publication instances of the patent applications. For EP...

Hello illxh, there are several issues with your query, basically, it can not work (for several reasons). The main problem is that you try to make 1 SQL query that one way or another would result in all the data attributes you need (applicants, inventors, grant date, forward and backward citations, c...

Hello khanhhienbi, your query does not count forward citations. On the forum there are quite some posts that explain the concept of forward citations. Looking at the WHERE clause of your query, I assume you want to count the forward citations of patents filed at the UK patent office that have been g...

Hello Sina, in principle, that is possible, but you will need to develop a model/methodology to define which patents exactly you would consider ESG relevant. I fear that simply taking all patents tagged with cpc_class_symbol = 'Y02' or 'Y04S' will be too broad. You could see your question as a varia...

Have a look at the NUTS codes in tls206_person and table tls904_nuts; it might save you some work.

Hello Lorens, All your observations are due to a small mistake in your query, you should have "FROM tls201_appln JOIN tls225_docdb_fam_cpc ON tls201_appln. docdb_family_id = tls225_docdb_fam_cpc. docdb_family_id ". (I left the mistake in your post to make it clear for others users.) Basica...

Hello Hien, A second medical use (further medical use according to Article 54(5) EPC) means that the compound was used in the past as a medicament, but for the treatment of a different disease. From classification point of view, there is no possibility to distinguish first or further medical uses , ...

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