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
Data Wrangling with SQL
Raghav Kandarpa | Shivangi Saxena
Jul, 2023 | 350 pages
Small book image
The SQL Workshop
Frank Solomon | Awni Al Saqqa | Prashanth Jayaram
Dec, 2019 | 288 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

Introduction

In Chapter 1, Understanding and Describing Data, we discussed analytics and how we can use data to obtain valuable information. While we could, in theory, analyze all data by hand, computers are far better at the task and are certainly the preferred tool for storing, organizing, and processing data. Among the most critical of these data tools is the relational database and the language used to access it, Structured Query Language (SQL). These two technologies have been cornerstones of data processing and continue to be the data backbone of most companies that deal with substantial amounts of data.

Companies use SQL as the primary method for storing much of their data. Furthermore, companies now take much of this data and put it into specialized databases called data warehouses and data lakes so that they can perform advanced analytics on their data. Virtually all of these data warehouses and data lakes are accessed using SQL. We'll be looking at working with SQL...

Previous Section
End of Section 2
Next Section