Help with backward citations and patent counts

[Hanna]

Hello!

I am currently writing my master thesis in economics. The purpose is to examine the role of inter-industry knowledge spillovers between the technological field of photovoltaic energy and other technologies. To do this I’m using patent citation data and patent counts.

I am however new to SQL and would be most grateful for any support I can get regarding this.
I’m using PATSTAT online and want to download two data sets.

First:
I want to count all patent applications belonging to the three technological fields I refer to as PV, Solar and General, grouped by their priority year. The CPC-classification is used to distinguish the three groups, so that PV is a list of CPC-symbols that is used for photovoltaic energy technology.
The syntax I have come up with is
SELECT COUNT(tls201_appln.appln_id), tls201_appln.prior_earliest_year
FROM tls201_appln, tls224_appln_cpc
WHERE tls224_appln_cpc.appln_id=tls201_appln.appln_id
AND tls224_appln_cpc.cpc_class_symbol like "Y02E10/5%”
OR tls224_appln_cpc.cpc_class_symbo l like "H02N6%"
GROUP BY tls201_appln.prior_earliest_year

Could anyone say if this is correct? When enter this in PATSTAT the search process takes so long time I’m not sure it is ever going to finish. If the data set is too big for the computer to handle I can limit my search to only include EP-patents.

The second is the more tricky one:
I want to count the CPC-classes in each of the three categories PV, Solar and General that are given to patents that have been cited by EP-patents given the CPC-class Y02E10/50, also here grouped by the priority year of the citing patent.

Could anyone help me to find the correct syntax for this?

Necessary conditions to be fulfilled are:
1. Citing patent has the CPC-class="Y02E10/50":
- tls224_appln_cpc.cpc_class_symbol= "Y02E10/50" --
2.. Citing patent is an EP application of type A1 or A2:
- tls201_appln.appln_kind= ‘A1 ‘ or tls201_appln.appln_kind= ‘A2’
3. Cited document is a patent
- tls212_citation.pat_citn_seq_nr<>0
4. no duplicates among cited (per each citing)
- COUNT(DISTINCT(`CITED_PAT_PUBLN_ID`)) FROM `TLS212_CITATION`

I would be truly grateful if someone could help me with just a little piece of this.

Best regards,
Hanna

[nico.rasters]

Regarding your first question:

SELECT YEAR(`tls201_appln`.`appln_filing_date`), COUNT(*)
FROM `tls201_appln`
INNER JOIN `tls224_appln_cpc` ON `tls201_appln`.`appln_id` = `tls224_appln_cpc`.`appln_id`
WHERE `cpc_class_symbol` LIKE "Y02E__10/5%" OR `cpc_class_symbol` LIKE "H02N___6%"
GROUP BY YEAR(`tls201_appln`.`appln_filing_date`);

I don't have access to PATSTAT Online so I replaced prior_earliest_year with appln_filing_date.
Also, in LIKE "Y02E__10/5%" I have used _ to indicate a space. It is very important to put two spaces here, and three spaces before the 6 in "H02N 6%" otherwise you'll get zero results.

The query above took 2 min 10.19 sec on my workstation. If it takes much longer in the online environment then they probably did not put an index on the cpc_class_symbol field.

You don't have to mention INNER JOIN as far as I know. Your JOIN with the WHERE criteria should work equally well.

Will get back to you on your second problem after I've reread it a few times