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

Date and Time Data Types for Analysis

We are all familiar with dates and times, but we don't often think about how these quantitative measures are represented. Yes, they are represented using numbers, but not with a single number. Instead, they are measured with a set of numbers, one for the year, one for the month, one for the day of the month, one for the hour, one for the minute, and so on.

What we might not realize, though, is that this is a complex representation, comprising several different components. For example, knowing the current minute without knowing the current hour is useless. Additionally, there are complex ways of interacting with dates and times, for example, different points in time can be subtracted from one another. Additionally, the current time can be represented differently depending on where you are in the world.

As a result of these intricacies, we need to take special care when working with this type of data. In fact, Postgres, like most databases...

Previous Section
End of Section 3
Next Section