Week 67 - Stored Procedures & UDFs

This week, we're moving forward with our previous challenge from Week 65 and are showing
our preference for semi-structured data and will output our very own Object!

This week, we’re again diving into the “Cybersyn US Patent Grants” dataset from the Market Place and using our own functions to interact with it.

The fascinating aspect of semi-structured data and VARIANT column types in Snowflake is that they offer a flexible way to handle diverse data formats. They act as a dynamic repository, allowing you to store and analyze multi-faceted information without being restricted to a rigid schema. Moreover, combining these with Snowflake's capability to store and retrieve objects empowers the entire team with a scalable data model. And here's a bonus: you can leverage these features 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 builds an Object in a VARIANT column, comparable to the screenshot given beneath. The function from week 65 hasn't changed much: Calculate The gap between APPLICATION_DATE and PUBLICATION_DATE can be 365 days for “Reissue” patents and 2 years for “design” ones. Have a true or false designate if we're WITHIN the parameter set for either patent

• As a last objective; Get the inside_of_projection values, into a different column (meaning that we want you to get it out of the object)