opposition count by ipc 4digit

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

Elisa_D
Posts: 13
Joined: Wed Jan 26, 2022 10:38 am

opposition count by ipc 4digit

Post by Elisa_D » Sat May 27, 2023 9:05 am

Dear Community, 
I would like to retrieve the number of oppositions in all the IPC-4 digit classes. I don't know if there is a way to avoid explicitly listing in the query all the IPC-4 digit, but make it automatic. If not, how could I structure my query? I don't mind if a single patent that pertains to multiple IPCs is counted as many times as its specifications. 
Thank you so much for your precious help.
Best regards
Elisa

Edit: I tryed with the following query

Code: Select all

SELECT LEFT(aipc.ipc_class_symbol, 4), COUNT(a.appln_id) AS 'opp_count'
FROM tls201_appln a 
 join tls211_pat_publn pp on a.appln_id = pp.appln_id
 join tls209_appln_ipc aipc on a.appln_id = aipc.appln_id
 join tls207_pers_appln pa on a.appln_id = pa.appln_id
 join tls206_person pe on pa.person_id = pe.person_id
 join reg101_appln re on a.appln_id = re.appln_id
 join reg130_opponent op on re.id = op.id
WHERE a.appln_kind = 'A' -- Patent (exclude the PCT applications that have not yet entered reg. phase.)
AND a.appln_id < 900000000 -- exclude artificial applications
AND a.ipr_type = 'PI'
AND a.granted = 'Y'
AND a.appln_auth = 'EP'
AND pe.psn_sector = 'COMPANY'
AND pp.publn_kind = 'B1'
AND op.oppt_name not like 'NULL'
AND len(aipc.ipc_class_symbol) >= 4
GROUP BY aipc.ipc_class_symbol
ORDER BY opp_count DESC
Is this correct?
Thank you so much,
Elisa


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

Re: opposition count by ipc 4digit

Post by EPO / PATSTAT Support » Tue May 30, 2023 11:46 am

Hello Elisa,
I changed a couple of small things in your query. And I removed (by making SQL lines as comments) a couple of the conditions which are not wrong, but not necessary neither.
All information on opposition procedures in PATSTAT Register are linked to granted EP patent applications.
So they all have a B1 publication, they are all granted, they are all PI, appln_kind = 'A', and they should never have an appln_id > 900000000 (replenished applications).
The only substantial change I made (but on your discretion, you can undo it) is to use a COUNT(distinct(a.appln_id)). This avoids a patent to be counted multiple times when there is more then 1 opponent, or when information (like an address or the name of agent) of the opponents change which results in a "new set" of opponents records (see use of "is-latest"). With other words, a count(distinct...), only counts the application 1 time, independent from the amount of opponents, or the number of times the opponent information has been changed or corrected. The count-distinct will also eliminate double counting when a patent would have more then 1 IPC code starting with the same 4 digits.
Your "company" condition will of course also exclude all patents which were not flagged as company. Universities, inventor/applicants, etc.. (see sector definition in the data catalog).

Code: Select all

SELECT LEFT(aipc.ipc_class_symbol, 4), COUNT(distinct(a.appln_id)) AS 'opp_count'
FROM tls201_appln a 
 join tls211_pat_publn pp on a.appln_id = pp.appln_id
 join tls209_appln_ipc aipc on a.appln_id = aipc.appln_id
 join tls207_pers_appln pa on a.appln_id = pa.appln_id
 join tls206_person pe on pa.person_id = pe.person_id
 join reg101_appln re on a.appln_id = re.appln_id
 join reg130_opponent op on re.id = op.id
WHERE --a.appln_kind = 'A' -- Patent (exclude the PCT applications that have not yet entered reg. phase.)
-- AND a.appln_id < 900000000 -- exclude artificial applications
-- AND a.ipr_type = 'PI'
-- AND a.granted = 'Y'
-- AND a.appln_auth = 'EP'
--AND 
pe.psn_sector = 'COMPANY'
-- AND pp.publn_kind = 'B1'
-- AND op.oppt_name not like 'NULL'
-- AND len(aipc.ipc_class_symbol) >= 4
GROUP BY aipc.ipc_class_symbol
ORDER BY opp_count DESC
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply