Changes

Jump to navigation Jump to search
391 bytes added ,  18:41, 17 February 2020
Accordingly, I retrieved all of UVM's patent filings to the USPTO from [https://cloud.google.com/blog/products/gcp/google-patents-public-datasets-connecting-public-paid-and-private-patent-data Google Patents Public Dataset] using the following SQL in BigQuery:
WITH Patent_Matches AS( (SELECT PARSE_DATE('%Y%m%d', SAFE_CAST(ANY_VALUE(patentsdb.filing_date) AS STRING)) AS Patent_Filing_yearPatent_Filing_Date, patentsdb.application_number AS Patent_Application_Number, ANY_VALUE(assignee_name.name) AS AssigneeName FROM `patents-public-data.patents.publications` AS patentsdb, UNNEST(assignee_harmonized) AS assignee_name WHERE LOWER(assignee_name.name) LIKE '%univ vermont%' AND patentsdb.country_code = 'US' GROUP BY Patent_Application_Number ), Date_Series_Table AS ( SELECT day, 0 AS Number_of_Patents FROM UNNEST (GENERATE_DATE_ARRAY( (SELECT MIN(Patent_Filing_Date) FROM Patent_Matches), (SELECT MAX(Patent_Filing_Date) FROM Patent_Matches) )) AS day ) SELECT Patent_Filing_yearSAFE_CAST(FORMAT_DATE('%Y-%m',Date_Series_Table.day) AS STRING) AS Patent_Date_YearMonth, COUNT(Patent_Matches.Patent_Application_Number) AS Number_of_Patent_Applications FROM Patent_Matches RIGHT JOIN Date_Series_Table ON Patent_Matches.Patent_Filing_Date = Date_Series_Table.day GROUP BY Patent_Filing_year Patent_Date_YearMonth ORDER BY Patent_Filing_yearPatent_Date_YearMonth;
Note that UVM's harmonized assignee name is UNIV VERMONT. Also, I could only go up to 2017, as patent applications are typically not disclosed for 18 months to protect their investors.

Navigation menu