On the first query, you forgot the WHERE clause and as a result all the conditions became part of the JOIN condition. (and some small mistakes on your CPC codes.) SELECT person_ctry_code, COUNT(distinct(case when appln_filing_year = 1980 then tls201_appln.appln_id end)) as '1980', COUNT(distinct(cas...

I would like to add a couple of items on Elizabeths' answer (which is correct). The OECD document is from 2014; IPC and CPC codes migh have changed. But the Y-tags in the CPC scheme have absolutely changed and have been pruned. So you will also need to check if those Y-tags are still valid or maybe ...

Yes, the list gives you all the EP, US and JP patent family members for patents granted in (at least) those 3 geographical areas. The pre-agregated forward citations count is based on a family/family relation, but is not linked to the fact whether the citing applications are also from EP, US or JP. ...

There was a small mistake in the query, the WHERE clause specified AND a.appln_filing_date >= '2010-01-01' AND a.appln_filing_date <= '2021-12-31' as well as AND a.appln_filing_year= '2010' , and as a result only the year 2010 was taken. I corrected the above query and removed the limitation of the ...

Hello KenoHaverkamp, no new data for the PSN name or sector was added to the data for the PATSTAT 2022b release. We recently started a new contract to receive the harmonised names, so we hope this will be be reflected in the new Spring 2023 release. Your query is ok, keep in mind that the country co...

Hello Damian, on i) Each patent belongs to exactly 1 DocDB (and INPADOC) patent family, so also the patents that have no family members. A single patent filed in ES, will have no other patent family members. Via the docdb_family_size attribute, there is an easy and quick way to identify those "...

The EPO has started to produce short explanatory video clips covering specific topics, the so-called Patent Knowledge Nuggets, which are accessible on the EPO’s YouTube channel Patent Knowledge Nuggets playlist . The following clips are related to patent analytics and patent insight reports: Basic p...

In 2014, the PRS data was sold as a separate product (compatible with PATSTAT) and the table was named TLS221_INPADOC_PRS Now the PRS data (= INPADOC data) is standard included in the PATSTAT data base as table tls231_inpadoc_legal_event . The tls803_legal_event_code is only a reference table to fac...

Hello Munai, appln_id=523137204 should indeed have 6 IPC codes in PATSTAT 2022 Autumn edition. SELECT [appln_id] ,[ipc_class_symbol] ,[ipc_class_level] ,[ipc_version] ,[ipc_value] ,[ipc_position] ,[ipc_gener_auth] FROM tls209_appln_ipc where appln_id=523137204 appln_id ipc_class_symbol ipc_class_lev...

Hello Carlos, full text searching is probably the best way to go. Your example SQL query will look for applications having specific single words as title or abstract which is not the same as a certain word to be present in the title or the abstract. Have a look at the "contains" function i...