Frosty Friday Consultants has been hired by the University of Frost’s history department; they want data on monarchs in their data warehouse for analysis. Your job is to take the JSON file located here, ingest it into the data warehouse, and parse it into a table that looks like this:
End Result:
If you can’t read the above right-click and view image in another tab.
- Separate columns for nicknames and consorts 1 – 3, many will be null.
- An ID in chronological order (birth).
- An Inter-House ID in order as they appear in the file.
- There should be 26 rows at the end.
Hints:
- Make sure you don’t lose any rows along the way.
- Be sure to investigate all the outputs and parameters available when transforming JSON.
Remember if you want to participate:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.
If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.
26 responses to “Week 4 – Hard”
-
I immediately tried using the new features!
- Solution URL – https://github.com/allllllllez/frosty_friday/blob/main/sql/week-4.sql
-
The query for semi-structured data was very interesting.
- Solution URL – https://github.com/gakut12/Frosty-Friday/blob/main/week4_hard_json/week4.sql
-
😉
- Solution URL – https://github.com/BehnazHeidaridehoee/frostyfriday/blob/main/week4/frosty_friday_week4
Leave a Reply
You must be logged in to post a comment.