Read Time: 3 minutes

Morning crunchers! The Query here — the data newsletter that's like a PowerBI dashboard for your data career. We'll provide you with at-a-glance insights and an easy way to filter out irrelevant data.

Here’s what we have for you today:

• Our course on showcasing your data portfolio is now live 🎉 

• SQL cheatsheet PDF and desktop wallpaper download 🤓 

• Avoiding SQL errors with safe_cast

• Join jokes 😂 

def learn_data_analysis(👨‍💻):

1. Our course on showcasing your data portfolio to land interviews is live! 

In this course you’ll learn:

• Why Substack is our recommended platform for creating your portfolio

• How to use the templates we provide to create a winning About Me Page and portfolio project write ups.

• Our approach to using video in your portfolio write-ups and a video script template to help you stand out when applying for jobs

• How to write for the internet (probably the largest area of improvement for most portfolio projects we see)

• And a whole lot more…

You can check out the course here and for the next week, we’re giving all newsletter subscribers 50% off — just use the code NEWSLETTER at checkout.

2. SQL cheatsheet PDF and desktop wallpaper download 😃 

On Monday, we posted our SQL cheatsheet on LinkedIn and mentioned we’d be sending a link where you can download a PDF and Desktop wallpaper version of it.

Here’s the link to download straight from G Drive 🤓 

select * from dataset-of-the-week

Futball, aka soccer, aka the most popular sport in the world, is a thing of passion for many people.

While we do recommend using datasets for portfolio projects that are relevant to the type of job you’re trying to get, datasets for topics of personal passions are fantastic too.

Here’s why:

1. You have more context into the topic which means you’ll have a deeper understanding of any trends you see

2. You’ll be more invested and engaged in the analysis and that shows in the final results.

So of course, this week’s dataset of the week in the FIFA World Cup 2020: Complete Dataset by Iron486.

There are 88 columns in this dataset which means there are so many different places you can go with your analysis.

Good luck!

class MiniLesson:

Avoid SQL Errors with SAFE_CAST

Often times in our analytics work we will want to CAST a column to another data type.

For example, we might have a column containing numbers but the data type of that column is a STRING.

To work with these strings as numbers we use the CAST function.

An example of this is: CAST(column_name as NUMERIC)

But one problem with this is it can cause your whole query to error out if any single row fails to cast to another data type.

In this example, this could happen if one of the rows mistakenly had a word in it instead of a number.

To avoid errors you can use the SAFE_CAST function.

SAFE_CAST is a SQL function that's available in many data warehouses like Google BigQuery.

SAFE_CAST allows you to safely cast or convert data from one data type to another.

If the conversion cannot be performed, instead of causing your query to crash or return an error, the SAFE_CAST function will return NULL.

Using SAFE_CAST can help you avoid unexpected errors in your queries when dealing with data that may not be in the expected format or contains invalid values for the target data type.

Here's an example to illustrate its usage…

Suppose we have a table named raw_data with the following schema and data.

We want to cast the value column to integers, but there is a record (record_id 2) with an invalid value for an integer.

If we try to cast the value column directly, we may encounter an error:

To avoid this error and handle the casting safely, we can use the SAFE_CAST function:

In this example, the SAFE_CAST function takes the value and the target data type (INT64) as arguments.

When the value cannot be cast to the target data type, as in the case of record_id 2, the function returns NULL instead of causing an error.

If you want to put the errors in a separate table for further analysis, you can filter the records with NULL values in the int_value column:

Understanding how to use functions like SAFE_CAST to handle potential data type conversion errors in your SQL queries can be a big help when you need it.

It helps you maintain the stability and reliability of your analysis, allowing you to work with data that may contain unexpected or invalid values that could otherwise cause your queries to fail.

By using SAFE_CAST, you can also identify and analyze problematic records separately, improving the quality of your data processing and analysis.

import memes as 😂 

Learn from us 🤓 

1. LinkedIn

We create content on LinkedIn daily. You can follow Cody here and Kyle here.

2. YouTube

We’re slowly starting to create YouTube content. You can subscribe to the channel here.

3. Courses

Our course on showcasing your data portfolio is live! Check it out here.

4. Guides

We’ll be launching the ultimate guide to becoming a data analyst for free in the next 30 days.

This ~15k word beast will provide you with everything you need on your journey to landing your first Data Analyst job.

If you have any questions you’d like answered in the guide, reply to this email and we’ll make sure to include an in-depth and comprehensive answer in the guide.

That’s it for today.

Stay crunchin’ folks and see you next week!

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -