Well Data-Birds, March is about to leave us and so it is time to introduce the Data-Bird of the month!

With March being Women’s History month, we thought we would take a look at some of the women who blazed this trail before us. These will not be the regular deep data-bird dive, rather we will give you a brief summary of each and a list of resources to check out to learn more.

When I was in school, we didn’t learn about many of the contributions of these women, which made it difficult for many women to see themselves…


Temp Tables, CTEs, and Subqueries

In my last post, I walked you through some simple window functions. You can read that here. The final query in SQL:

WITH CTE as (SELECT date, state, county,
cases — LAG (cases,1) OVER(PARTITION BY
fips ORDER BY date)as ‘new_cases’,
cases as ‘cumulative_cases’FROM counties)
SELECT date, state, county, new_cases,cumulative_cases,
AVG(new_cases) OVER (PARTITION BY state,county
ORDER BY date ASC
rows 6 PRECEDING) as ‘7_day_avg_new’
FROM CTE
ORDER BY state, county, date

We used a CTE in this query in order to give us a sort of temporary table to query, so this seems like a good…


Window Function Fun!

This is the sixth post in a series on SQL.

My fifth post on SQL was more on joins and query structure. You can read that here.

To recap, in the last post, we discussed INNER and LEFT joins and how to go about building a query. We also rewrote the query from part 4 using a join instead of a subquery.

Written in SQL:

SELECT c.state, c.county, c.cases, c.deaths, c.cases/c.deaths as ‘case/death ratio’, m.ALWAYS FROM counties as c JOIN mask_use as m on c.fips = m.countyfp JOIN election as e on c.fips = e.fips ORDER BY…


Joins and building a query.

My fourth post on SQL was an introduction to subqueries. You can read that here.

To recap, in the last post, I walked you through building a simple subquery that functioned as a filter for our data.

Written in SQL:

SELECT state, county, cases, deaths, 
cases/deaths as ‘case/death ratio’, ALWAYS
FROM counties
JOIN mask_use ON fips = countyfp
WHERE fips IN
(SELECT fips
FROM election
ORDER BY clf_unemploy_pct DESC
LIMIT 25)
AND date = ‘2020–09–07’

If you remember, in the last post the query of the employment table was run, and then we took the…


Basic subquery and order of operations

This is the fourth post in a series on SQL.

My third post on SQL was an overview of basic aggregate functions. You can read that here.

To recap, in the last post, I walked you through building a simple query that aggregated data in selected columns from 2 tables and filtered for conditions.

Written in SQL:

SELECT state, COUNT (county), MIN (deaths), MAX (deaths), AVG (deaths),SUM (cases)/SUM (deaths) as ‘case/death ratio’, AVG (ALWAYS) FROM counties JOIN mask_use ON fips = countyfp WHERE deaths <= ‘500’ AND date = ‘2020–09–07’ GROUP BY state HAVING…


Basic Aggregate Functions

This is the third post in a series on SQL.

My second post on SQL was an overview of SELECT, FROM, WHERE, and JOIN. You can read that here.

To recap, in the last post, I walked you through building a simple query that selected columns from 2 tables and filtered for conditions

Written in SQL:

SELECT date, state, county, deaths, FREQUENTLY, ALWAYS
FROM counties
JOIN mask_use on fips = countyfp
WHERE state = ‘Texas’
AND deaths > ‘500’
AND date = ‘2020–09–07’

In this post, we are again going to use data obtained from the New…


SELECT, FROM, JOIN, and WHERE statements

My first post on SQL was a very high-level overview of relational databases, queries, and what SQL is. You can read that here.

To recap, in the last post, I included an example where we asked the database to find the name ‘Mutt Barkely’ in the customer table and then match the CustomerID from that row to the CustomerID in the order table and return any orderID where there was a match.

Written in SQL:

SELECT OrderId
FROM Orders
JOIN Customers on Customers.CustomerID = Orders.CustomerID
WHERE Customers.Name = ‘Mutt Barkley’

In this post, we…


Introduction to relational databases

This is the first post in a series on SQL.

According to an IADSS article* SQL is the second most common skill listed in data science job postings — only python is more common. This makes SQL a core skill for any data scientist, data analyst, or data engineer. So what is SQL and why is it so important to data scientists? This post is meant to be a very basic, high-level overview of what SQL is and why it is important to have this skill if you are pursuing a data career.

Our focus on…

Amandaspotter

Chicago gal, obsessed with data.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store