meerens / 8-week-sqlchallenge Goto Github PK

Greetings 👋🏼 - my name is Marian 👩🏼‍💻 and I recently embarked on a journey to level up my SQL game by starting the 8 Week SQL Challenge by Danny Ma.

This repository contains all my documentation for the challenges that I have completed so far. Keep watching 👀 this space as I add on more solutions.

Solutions were coded in Google BigQuery syntax.

Each case-study folder contains the following files

• data.sql: compiled sql for creating tables & loading data (if no data.sql file, you'll find csv files instead)
• datacleaning.sql: compiled sql for cleaning data
• solution.sql: compiled sql for answering the challenge questions

My code was written in a Count SQL notebook.

From there I copied the compiled SQL to a *.sql file which you can view in the individual challenge folders.

I also copied the markdown from the notebook to a Craft doc to which I added some additional formatting together with the screenshots and/or csv downloads so you can see tabular output from the individual notebook cells.

Check the links below for all the documentationion for each individual challenge.

🔗 The Challenge
🔗 SQL Notebook
🔗 Markdown / Solution Comments

Lessons learnt

• The wonderous world of CTE's or Common Table Expressions
• Using WINDOW functions like DENSE_RANK
• Using STRING_AGG to concatenate non-null values
• Wrapped CASE statements (to filter, to aggregate)

🔗 The Challenge
🔗 SQL Notebook
🔗 Markdown / Solution Comments

Lessons Learnt

• Creating an array from a string using STRING_AGG
• Flattening arrays using UNNEST / CROSS JOIN
• EXTRACT returns an integer for the corresponding date part
• PARSE_DATE converts the string representation of a date to a date
• DATE_DIFF calculates the difference between 2 days expressed in specified datepart
• Using Window aggregations
• Wrapped CASE statements (to filter, to aggregate)

🔗 The Challenge
🔗 SQL Notebook
🔗 Markdown / Solution Comments