Book Image

The Applied SQL Data Analytics Workshop - Second Edition

By : Matt Goldwasser, Upom Malik, Benjamin Johnston
3.5 (2)
Book Image

The Applied SQL Data Analytics Workshop - Second Edition

3.5 (2)
By: Matt Goldwasser, Upom Malik, Benjamin Johnston

Overview of this book

Every day, businesses operate around the clock and a huge amount of data is generated at a rapid pace. Hidden in this data are key patterns and behaviors that can help you and your business understand your customers at a deep, fundamental level. Are you ready to enter the exciting world of data analytics and unlock these useful insights? Written by a team of expert data scientists who have used their data analytics skills to transform businesses of all shapes and sizes, The Applied SQL Data Analytics Workshop is a great way to get started with data analysis, showing you how to effectively sieve and process information from raw data, even without any prior experience. The book begins by showing you how to form hypotheses and generate descriptive statistics that can provide key insights into your existing data. As you progress, you'll learn how to write SQL queries to aggregate, calculate and combine SQL data from sources outside of your current dataset. You'll also discover how to work with different data types, like JSON. By exploring advanced techniques, such as geospatial analysis and text analysis, you'll finally be able to understand your business at a deeper level. Finally, the book lets you in on the secret to getting information faster and more effectively by using advanced techniques like profiling and automation. By the end of The Applied SQL Data Analytics Workshop, you'll have the skills you need to start identifying patterns and unlocking insights in your own data. You will be capable of looking and assessing data with the critical eye of a skilled data analyst.
Table of Contents (9 chapters)
7. The Scientific Method and Applied Problem Solving

The World of Data

We will start with a simple question: what is data? Data can be thought of as the recorded measurements of something in the real world. For example, a list of heights is data; that is, height is a measure of the distance between a person's head and their feet. The object of the data is describing a unit of observation. In the case of these heights, a person is the unit of observation.

As you can imagine, there is a lot of data that we can gather to describe a person—including their age, weight, whether they are a smoker and more. One or more of these measurements used to describe one specific unit of observation is called a data point, and each measurement in a data point is called a variable (this is also often referred to as a feature). When you have several data points together, you have a dataset.

Types of Data

Data can also be broken down into two main categories, quantitative and qualitative:

Figure 1.1: The classification of types of data

Figure 1.1: The classification of types of data

Quantitative data is a measurement that can be described as a number; qualitative data is data that is described by non-numerical values, such as text. Your height is data that would be described as quantitative. However, describing yourself as either a "smoker" or a "non-smoker" would be considered qualitative data.

Quantitative data can be further classified into two subcategories: discrete and continuous. Discrete quantitative values are values that can take on a fixed level of precision—usually integers. For example, the number of times you've had surgery in your life is a discrete value; for instance, you can have surgery 0, 1, or more times, but you cannot have surgery 1.5 times. A continuous variable is a value that, in theory, could be divided into an arbitrary amount of precision. For example, your body mass could be described with arbitrary precision to be 55, 55.3, 55.32, and so on. In practice, of course, measuring instruments limit our precision. However, if a value could be described with higher precision, then it is generally considered continuous.

It's important to note that qualitative data can generally be converted into quantitative data, and quantitative data can also be converted into qualitative data.

Let's think about this using the example of being a "smoker" versus a "non-smoker." While you can describe yourself to be in the category of "smoker" or "non-smoker," you could also reimagine these categories as responses to the statement "you smoke regularly," and then use the Boolean values of 0 and 1 to represent "true" and "false," respectively.

Similarly, in the opposite direction, quantitative data (such as height) can be converted into qualitative data. For example, instead of thinking of an adult's height as a number in inches or centimeters (cm), you can classify them into groups, with people greater than 72 inches (that is, 183 cm) in the category of "tall," people between 63 inches and 72 inches (that is, between 160 and 183 cm) as "medium," and people shorter than 63 inches (that is, 152 cm) as "short."

Data Analytics and Statistics

Raw data by itself is simply a group of values. However, it is not very interesting in this form. It is only when we start to find patterns in the data and begin to interpret them that we can start to do interesting things such as make predictions about the future and identify unexpected changes. These patterns in the data are referred to as information. A large organized collection of persistent and extensive information and experience that can be used to describe and predict phenomena in the real world is called knowledge. Data analysis is the process by which we convert data into information and, thereafter, knowledge. When data analysis is combined with making predictions, we then have data analytics.

There are a lot of tools that are available to make sense of data. One of the most powerful tools of data analysis is using mathematical techniques on datasets. One of these mathematical techniques is statistics.

Types of Statistics

Statistics can be further divided into two subcategories: descriptive statistics and inferential statistics.

Descriptive statistics are used to describe data. Descriptive statistics on a single variable in a dataset is called univariate analysis, while descriptive statistics that look at two or more variables at the same time is called multivariate analysis.

In contrast, inferential statistics think of datasets as a sample or a small portion of measurements from a larger group called a population. For example, a survey of 10,000 voters in a national election is a sample of the entire population of voters in a country. Inferential statistics are used to try to infer the properties of a population-based on the properties of a sample.


In this book, we will be primarily focusing on descriptive statistics. For more information on inferential statistics, please refer to a statistics textbook, such as Statistics by David Freedman, Robert Pisani, and Roger Purves.

Imagine that you are a health policy analyst and are given the following dataset with information about patients:

Figure 1.2: Healthcare data

Figure 1.2: Healthcare data

When given a dataset, it's often helpful to classify the underlying data. In this case, the unit of observation for the dataset is an individual patient because each row represents an individual observation, which is a unique patient. There are 10 data points, each with 5 variables. Three of the columns, Year of Birth, Height, and Number of Doctor Visits, are quantitative because they are represented by numbers. Two of the columns, Eye Color and Country of Birth, are qualitative.

Activity 1.01: Classifying a New Dataset

In this activity, we will classify the data in a dataset. You are about to start a job in a new city at an up-and-coming start-up. You're excited to start your new job, but you've decided to sell all of your belongings before you head off. This includes your car. You're not sure what price to sell it at, so you decide to collect some data. You ask some friends and family who have recently sold their cars what the makes of their cars were and how much they sold them for. Based on this information, you now have a dataset. The data is as follows:

Figure 1.3: Used car sales data

Figure 1.3: Used car sales data

These are the steps to perform:

  1. Determine the unit of observation.
  2. Classify the three columns as either quantitative or qualitative.
  3. Convert the Make column into quantitative data columns.


    The solution for this activity can be found via this link.

In this activity, we learned how to classify our data. In the next section, we will learn about various methods of descriptive statistics.