Week 65 - Stored Procedures & UDFs

This week, we're diving into the "Cybersyn US Patent Grants" dataset from the Market Place and use our own functions to interact with it.

The cool thing about user-defined functions (UDFs) is that they're like your personal toolkit. You can use them to save a specific calculation, share it with the whole team, and even tweak it centrally when business needs change. And guess what? You can use them on public datasets, like the one from Cybersyn.

So, here's the game plan:

• Grab the Cybersyn US Patent Grants dataset from the marketplace.
• Use Cybersyn's SQL query to dig up all patents related to Nvidia (we've put a limit of 10 in there so as not to overwhelm you with the results):

SELECT patent_index.patent_id, invention_title, patent_type, application_date, document_publication_dateFROM cybersyn_us_patent_grants.cybersyn.uspto_contributor_index AS contributor_indexINNER JOINcybersyn_us_patent_grants.cybersyn.uspto_patent_contributor_relationships AS relationshipsON contributor_index.contributor_id = relationships.contributor_idINNER JOINcybersyn_us_patent_grants.cybersyn.uspto_patent_index AS patent_indexON relationships.patent_id = patent_index.patent_idWHERE contributor_index.contributor_name ILIKE 'NVIDIA CORPORATION'AND relationships.contribution_type = 'Assignee - United States Company Or Corporation'LIMIT 10

• Create a function that gives a thumbs up (TRUE) or thumbs down (FALSE) based on this: The gap between APPLICATION_DATE and PUBLICATION_DATE can be 365 days for "Reissue" patents and 2 years for "design" ones.
  
  All clear? Let's get to it!