This is the sixth article in the learning SQL series:

Joins Overview

Joins in SQL are a way to combine related tables. Typically, this is done through a foreign key — a column in a table used to reference another table. By instructing SQL to combine the tables using like keys, we are able to write more complex queries and utilize columns from more than one table.

Types of Joins

There are multiple different…


Forecast Trend and Residuals, Adding Back Seasonality

Overview

Part 1 Recap

A prior article in this series reviewed how to use seasonal decomposition to parse out seasonal and trend components. In this article, the trend and residual components of our seasonal decomposition will be used to make a time series forecasting model. Then, seasonal components will be added back to see how the full forecast looks compared to actuals.

Review Part 1: How To Find Seasonality Using Python, which covered these steps:

  1. Overview of the data: We’ll be forecasting Kansas City Crime data — more specifically the number of Breaking & Entering crimes per month.
  2. Loading Data: Data…


Parse Trend and Seasonality Components from a Time Series

Parsing seasonality from time series data can often be useful in data analytics. It helps with analyzing seasonality for decision making as well as for more accurate forecasts. Python can be used to separate out these trend and seasonal components.

Dataset

Overview

The time series data we’ll be analyzing is the Kansas City Crime Summary — more specifically the number of Breaking & Entering crimes per month. Do you think there will be any seasonal components to this data? Let’s find out.

Initial Visualization

To start, the original data is visualized below using Power BI — this helps to see any…


Using Aggregations to Roll Up Data

This is the fifth article in the learning SQL series:

Aggregating in SQL: GROUP BY Statements

Previous articles reviewed how to select relevant records from tables and apply filter conditions as needed. However, many times in reporting you want to aggregate results and perform additional calculations — such as counting the number of rows for a group or adding subtotals. This is where the GROUP BY statement comes in.

For this example, a new table will be used — FactResellerSales


Deploy End-To-End Machine Learning with PythonAnywhere, Flask, and Power BI

Machine Learning can be used to make predictions and cluster like data. How can we integrate some basic Machine Learning capabilities with Power BI and have a backend that handles the logic and dependencies? This article provides a basic how-to, showing one way to deploy a Python Machine Learning API and connect via Power BI.

Problem Overview

For demonstration, I created a fictitious dataset containing housing data. With only a handful of features, a rough guess can be made on how they will be clustered. …


This is the fourth article in the learning SQL series.

Filtering in SQL: More WHERE Statements

Previous articles reviewed how to setup your database environment, selecting records, and where statements used to filter records. In this article, where statements are expanded on. Similar to prior examples, this article will use the DimCustomer table in our AdventureWorks database.

In Statements

Part 3 reviewed how to write multiple conditions, separating with the AND keyword. However, if there’s a situation where you want to filter a particular column to a list of values, the IN keyword comes in handy. In the below query, the top 10 records are pulled where the…


This is the third article in the learning SQL series. Check out the first two parts on setting up your environment and basic select statements.

Filtering in SQL: The WHERE Statement

Previous articles reviewed how to setup your database environment and selecting records. In practice however, you’re typically interested in analyzing a subset of specific records. This is where the SQL WHERE statement comes in. The keyword is located towards the end of your SQL query after your FROM statement and any other references to table names (such as joins, which will be covered in a later article).

Continuing on the example from prior articles in…


This is the second article in the SQL series. For the last article on setting up your environment, read here.

SQL 101: A Select Statement

Once your environment is setup, the first thing you need to know how to do is pull data from a table. SQL is pretty easy to read and the gist of a SELECT statement is to provide the names of the columns and table you are pulling data from. Execute the statement and you’ll see the database return the rows and columns that you requested. You could pull data from one column, a few columns, or all the columns in…


Image for post
Image for post

I’m often asked how accessible SQL as a programming language is for those looking to build Data Analytics or BI skills. My answer is always very! I’m of the belief that anyone with the interest can learn SQL. The next question is often where to start, I’m hoping this will help. This is the first article in a series where I’ll be walking you through the basics of SQL in short articles.

Installation and Setup

If you do not have a personal SQL environment setup on your PC, I highly recommend doing so. It will allow you to follow along with the examples…


Designing great user interfaces in Power BI is one way to increase use and adoption. Anything that makes the experience more intuitive or enjoyable might be the difference between all your hard work on a backend model seeing the light of day vs. sitting on the shelf. There are many different paths to go down in terms of design, with some great examples already out on the web — such as how to achieve shadowing/visual framing, using just objects built in PowerPoint.

Given the fact that many styling concepts boil down to shapes, colors, and shadows used behind other text…

Barrett Studdard

Data Analytics & Viz

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