Book Image

SQL for Data Analytics

By : Upom Malik, Matt Goldwasser, Benjamin Johnston
3 (1)
Book Image

SQL for Data Analytics

3 (1)
By: Upom Malik, Matt Goldwasser, Benjamin Johnston

Overview of this book

Understanding and finding patterns in data has become one of the most important ways to improve business decisions. If you know the basics of SQL, but don't know how to use it to gain the most effective business insights from data, this book is for you. SQL for Data Analytics helps you build the skills to move beyond basic SQL and instead learn to spot patterns and explain the logic hidden in data. You'll discover how to explore and understand data by identifying trends and unlocking deeper insights. You'll also gain experience working with different types of data in SQL, including time-series, geospatial, and text data. Finally, you'll learn how to increase your productivity with the help of profiling and automation. By the end of this book, you'll be able to use SQL in everyday business scenarios efficiently and look at data with the critical eye of an analytics professional. Please note: if you are having difficulty loading the sample datasets, there are new instructions uploaded to the GitHub repository. The link to the GitHub repository can be found in the book's preface.

Related Content you might be interested in

Current Title:

Small book image
SQL for Data Analytics
Upom Malik | Matt Goldwasser | Benjamin Johnston
Aug, 2019 | 386 pages
Small book image
Business Intelligence Career Master Plan
Danny Moncada | Eduardo Chavez
Aug, 2023 | 284 pages
Small book image
Mastering NLP from Foundations to LLMs
Meysam Ghaffari | Lior Gazit
Apr, 2024 | 340 pages
Small book image
Principles of Data Science
Sinan Ozdemir
Jan, 2024 | 326 pages
Table of Contents (11 chapters)
Preface
Preface
About the Book
Free Chapter
1
1. Understanding and Describing Data
1. Understanding and Describing Data
Introduction
The World of Data
Methods of Descriptive Statistics
Statistical Significance Testing
Summary
2
2. The Basics of SQL for Analytics
2. The Basics of SQL for Analytics
Introduction
Relational Databases and SQL
Basic Data Types of SQL
Reading Tables: The SELECT Query
Creating Tables
Updating Tables
Deleting Data and Tables
SQL and Analytics
Summary
3
3. SQL for Data Preparation
3. SQL for Data Preparation
Introduction
Assembling Data
Transforming Data
Summary
4
4. Aggregate Functions for Data Analysis
4. Aggregate Functions for Data Analysis
Introduction
Aggregate Functions
Aggregate Functions with GROUP BY
The HAVING Clause
Using Aggregates to Clean Data and Examine Data Quality
Summary
5
5. Window Functions for Data Analysis
5. Window Functions for Data Analysis
Introduction
Window Functions
Statistics with Window Functions
Summary
6
6. Importing and Exporting Data
6. Importing and Exporting Data
Introduction
The COPY Command
Using R with Our Database
Using Python with Our Database
Best Practices for Importing and Exporting Data
Summary
7
7. Analytics Using Complex Data Types
7. Analytics Using Complex Data Types
Introduction
Date and Time Data Types for Analysis
Performing Geospatial Analysis in Postgres
Using Array Data Types in Postgres
Using JSON Data Types in Postgres
Text Analytics Using Postgres
Summary
8
8. Performant SQL
8. Performant SQL
Introduction
Database Scanning Methods
Performant Joins
Functions and Triggers
Summary
9
9. Using SQL to Uncover the Truth – a Case Study
9. Using SQL to Uncover the Truth – a Case Study
Introduction
Case Study
Summary
Appendix
Appendix
1. Understanding and Describing Data
2. The Basics of SQL for Analytics
3. SQL for Data Preparation
4. Aggregate Functions for Data Analysis
5. Window Functions for Data Analysis
6. Importing and Exporting Data
7. Analytics Using Complex Data Types
8. Performant SQL
9. Using SQL to Uncover the Truth – a Case Study

Transforming Data

Often, the raw data presented in a query output may not be in the form we would like it to be. We may want to remove values, substitute values, or map values to other values. To accomplish these tasks, SQL provides a wide variety of statements and functions. Functions are keywords that take in inputs such as a column or a scalar value and change those inputs into some sort of output. We will discuss some very useful functions for cleaning data in the following sections.

CASE WHEN

CASE WHEN is a function that allows a query to map various values in a column to other values. The general format of a CASE WHEN statement is:

CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
…
WHEN conditionX THEN valueX
ELSE else_value END

Here, condition1 and condition2, through conditionX, are Boolean conditions; value1 and value2, through valueX, are values to map the Boolean conditions; and else_value is the value that is mapped if none of the Boolean...

Previous Section
End of Section 4
Next Section