The Query (aka Kyle and Cody) here 👋

Here’s what we have for you today:

• Aggregating dates with DATE_TRUNC 📆

• Working with unstructured data in SQL 👨‍💻

• Data analyst jobs and freelance gigs 💼

• Memes I can definitely relate to 🤣

Looking to become a Data Analyst? START HERE.

select * from data-jobs

remote, data jobs

Because who likes writing SQL from a busy office?

Check out this week’s featured jobs.

1. Data Analyst @ ASP — $54-100k per year

2. Data Analyst @ Red Ventures — $75-115k per year

3. Data Analyst @ Lume Deodorant — $100-110k per year

freelance gigs

Need work experience? Get real experience with real projects.

1. Mental Health Data Analyst — $18-50 per hour (apply here)

2. Tableau Help— $25 fixed price (apply here)

3. Excel Analytics — $40-75 per hour (apply here)

def content_spotlight(🔦):

One of those most popular data tools is dbt — I use it all the time for data transformations.

Turns out they actually have a pretty great blog too!

Here’s an article written by Kira Furuichi at dbt on the power of the DATE_TRUNC function in SQL.

She also explains how to use dbt macros to make functions like this easier to write. This is especially great material if you are interested in moving beyond data analytics into analytics engineering.

I too love this function and I’m sure you’ll get something out of this article!

class SQLMiniLesson:

Working with Unstructured Data in SQL

Kyle here 👋 — Learning to work with data that's not in a table format is a big step as a data analyst.

And it can be very confusing looking at first.

You’re probably used to seeing data formatted as rows and columns — like in a spreadsheet or SQL table.

JSON is just a different way to format data that’s not rows and columns.

JSON data is represented as key-value pairs, similar to a dictionary in Python.

Why is JSON important?

In today's interconnected world, data is everywhere and it's often served to us via APIs.

APIs use JSON to deliver data because it's light, easy to understand, and can be used by many programming languages.

Now, let's imagine you've received a JSON file from an API and you want to work with this data in SQL.

How do you do that? Let's explore.

Suppose we have a table named user_profiles with the following schema and data:

SQL has functions to help us handle JSON data.

One common SQL function is for working with JSON is the JSON_VALUE function.

This let’s us extract things from our JSON data.

In this example, the JSON_VALUE function takes two arguments:

• The JSON data column (profile_data)

• A JSON path expression ('$.name') that specifies the key to extract the value from.

The $.name JSON path expression refers to the "name" key in the JSON data.

Knowing how to extract specific values or manipulate JSON data can greatly enhance your ability to analyze and work with such data.

Remember, JSON and SQL might seem like different languages, but with a little practice, you'll be able to translate between them with ease. It's all part of your journey to becoming a versatile data analyst.

So don't be afraid of JSON!

Embrace it, play around with it, and soon, you'll be extracting and analyzing data like a pro. Happy analyzing!

import memes as 😂 

content & resources 🤓 

1. Become a Data Analyst Guide: Our full guide on what it takes to land a job as a data analyst.

2. Open Data Analyst Jobs: Find your next data job here!

3. Download our SQL Cheatsheet as a PDF and desktop wallpaper here. 

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

That’s it for today.

Stay crunchin’ folks and see you next week!

— Kyle & Cody

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