The Query (aka Kyle and Cody) here 👋

Here’s what we have for you today:

• 73 freshly posted data positions on the job board 💼

• A fun game you can use in your spreadsheets♟️

• A simpler way to filter on window functions in SQL 💻

• A meme that hits home a little too hard 🤣

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 3 featured jobs.

1. Data Analyst @ Mondelez — $82-105k per year

2. Data Analyst, Product @ Flock Safety — $120-150k per year

3. Lead Data Analyst, Sales @ ezCater — $129-162k per year

The feedback and ideas so far coming from you all about the job board has been fantastic! Keep it coming here.

freelance gigs

Need work experience? Get real experience with real projects.

1. Show attendance rates in PowerBI — $5 fixed price (apply here)

2. Excel Data Analysis — $100 fixed price (apply here)

3. Tableau formula help — $75 fixed price (apply here)

def content_spotlight(🔦):

How to make a Chess board in Google Sheets.

I came across this fun post on my LinkedIn feed and had to share it with you.

Ben Collins created a chess board in a Google Sheet with conditional formatting and an array formula!

Here is the Chess Board Formula:

=MAKEARRAY(8,8,LAMBDA(r,c,IFS( r=1,CHAR(9800+CHOOSE(c,20,22,21,19,18,21,22,20)), r=2,CHAR(9823), OR(r=3,r=4,r=5,r=6),, r=7,CHAR(9817), r=8,CHAR(9800+CHOOSE(c,14,16,15,13,12,15,16,14)))))

Just copy/paste this into a Google Sheet and it will generate all the pieces for you.

Then, if you copy/paste values you can cut and paste the pieces and play chess with someone!

I am all for making spreadsheets fun! 😊

class MiniLesson:

A Cleaner Way to Filter on Window Functions

Kyle here 👋 — When I first started using window functions to rank data (like row_number) it made my queries messy.

I didn’t like that I had to use a CTE and a separate WHERE clause below to filter by the result of my window function (ex. WHERE it equals 1).

The problem is the window function in the SELECT gets executed after the WHERE clause.

The QUALIFY statement in SQL is a handy tool for making your queries more concise and readable.

I use it all the time when I’m writing SQL in BigQuery

It allows you to filter the results of a query based on the result of a window function, such as ROW_NUMBER().

This can be particularly useful when you want to select a single row for each group based on specific criteria, without the need to write a separate Common Table Expression (CTE) or subquery.

Let's consider a sales dataset with the following columns:

order_id, customer_id, product_id, sale_date, and sale_amount

Suppose you want to find the most recent purchase for each customer.

You can use QUALIFY along with ROW_NUMBER() to achieve this without using a CTE or subquery.

Here's an example of how to do this with and without QUALIFY, so you can see the benefit:

In this query, the ROW_NUMBER() window function assigns a row number for each row within each group of customer_id, with the row number 1 assigned to the most recent purchase (based on sale_date).

The QUALIFY statement then filters the results to only include rows with a row number of 1, effectively returning the most recent purchase for each customer.

QUALIFY in SQL can be a powerful tool for data analysts, simplifying queries and improving overall readability and performance.

Remember QUALIFY next time you are working with window functions!

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

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