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

Best Practices for Importing and Exporting Data

At this point, we have seen several different methods for reading and writing data between our computer and our database. Each method has its own use case and purpose. Generally, there are going to be two key factors that should guide your decision-making process:

  • You should try to access the database with the same tool that you will use to analyze the data. As you add more steps to get your data from the database to your analytics tool, you increase the ways in which new errors can arise. When you can't access the database using the same tool that you will use to process the data, you should use psql to read and write CSV files to your database.
  • When writing data, you can save time by using the COPY or \copy commands.

Going Password-Less

In addition to everything mentioned so far, it is also a good idea to set up a .pgpass file. A .pgpass file specifies the parameters that you use to connect to your database...

Previous Section
End of Section 6
Next Section