Book description
Take your first steps to become a fully qualified data analyst by learning how to explore large relational datasets
Key Features
- Explore a variety of statistical techniques to analyze your data
- Integrate your SQL pipelines with other analytics technologies
- Perform advanced analytics such as geospatial and text analysis
Book Description
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.
What you will learn
- Perform advanced statistical calculations using the WINDOW function
- Use SQL queries and subqueries to prepare data for analysis
- Import and export data using a text file and psql
- Apply special SQL clauses and functions to generate descriptive statistics
- Analyze special data types in SQL, including geospatial data and time data
- Optimize queries to improve their performance for faster results
- Debug queries that won't run
- Use SQL to summarize and identify patterns in data
Who this book is for
If you're a database engineer looking to transition into analytics, or a backend engineer who wants to develop a deeper understanding of production data, you will find this book useful. This book is also ideal for data scientists or business analysts who want to improve their data analytics skills using SQL. Knowledge of basic SQL and database concepts will aid in understanding the concepts covered in this book.
Table of contents
- SQL for Data Analytics
- Preface
-
1. Understanding and Describing Data
- Introduction
- The World of Data
-
Methods of Descriptive Statistics
- Univariate Analysis
- Data Frequency Distribution
- Exercise 1: Creating a Histogram
- Exercise 2: Calculating the Quartiles for Add-on Sales
- Central Tendency
- Exercise 3: Calculating the Central Tendency of Add-on Sales
- Dispersion
- Exercise 4: Dispersion of Add-on Sales
- Bivariate Analysis
- Scatterplots
- Exercise 5: Calculating the Pearson Correlation Coefficient for Two Variables
- Activity 2: Exploring Dealership Sales Data
- Working with Missing Data
- Statistical Significance Testing
- Summary
- 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
- Introduction
- The COPY Command
- Using R with Our Database
-
Using Python with Our Database
- Why Use Python?
- Getting Started with Python
- Exercise 20: Exporting Data from a Database within Python
- Improving Postgres Access in Python with SQLAlchemy and Pandas
- What is SQLAlchemy?
- Using Python with Jupyter Notebooks
- Reading and Writing to our Database with Pandas
- Performing Data Visualization with Pandas
- Exercise 21: Reading Data and Visualizing Data in Python
- Writing Data to the Database Using Python
- Improving Python Write Speed with COPY
- Reading and Writing CSV Files with Python
- Best Practices for Importing and Exporting Data
- Summary
- 7. Analytics Using Complex Data Types
-
8. Performant SQL
- Introduction
-
Database Scanning Methods
- Query Planning
- Scanning and Sequential Scans
- Exercise 26: Interpreting the Query Planner
- Activity 10: Query Planning
- Index Scanning
- The B-tree Index
- Exercise 27: Creating an Index Scan
- Activity 11: Implementing Index Scans
- Hash Index
- Exercise 28: Generating Several Hash Indexes to Investigate Performance
- Activity 12: Implementing Hash Indexes
- Effective Index Use
- Performant Joins
-
Functions and Triggers
- Function Definitions
- Exercise 30: Creating Functions without Arguments
- Activity 14: Defining a Maximum Sale Function
- Exercise 31: Creating Functions with Arguments Using a Single Function
- Activity 15: Creating Functions with Arguments
- Triggers
- Exercise 32: Creating Triggers to Update Fields
- Activity 16: Creating a Trigger to Track Average Purchases
- Killing Queries
- Exercise 33: Canceling a Long Query
- Activity 17: Terminating a Long Query
- Summary
-
9. Using SQL to Uncover the Truth – a Case Study
- Introduction
-
Case Study
- Scientific Method
- Exercise 34: Preliminary Data Collection Using SQL Techniques
- Exercise 35: Extracting the Sales Information
- Activity 18: Quantifying the Sales Drop
- Exercise 36: Launch Timing Analysis
- Activity 19: Analyzing the Difference in the Sales Price Hypothesis
- Exercise 37: Analyzing Sales Growth by Email Opening Rate
- Exercise 38: Analyzing the Performance of the Email Marketing Campaign
- Conclusions
- In-Field Testing
- Summary
-
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
- Activity 10: Query Planning
- Activity 11: Implementing Index Scans
- Activity 12: Implementing Hash Indexes
- Activity 13: Implementing Joins
- Activity 14: Defining a Maximum Sale Function
- Activity 15: Creating Functions with Arguments
- Activity 16: Creating a Trigger to Track Average Purchases
- Activity 17: Terminating a Long Query
- 9. Using SQL to Uncover the Truth – a Case Study
Product information
- Title: SQL for Data Analytics
- Author(s):
- Release date: August 2019
- Publisher(s): Packt Publishing
- ISBN: 9781789807356
You might also like
book
SQL for Data Analytics - Third Edition
Take your first steps to becoming a fully qualified data analyst by learning how to explore …
book
SQL for Data Analysis
With the explosion of data, computing power, and cloud data warehouses, SQL has become an even …
video
Advanced Applied SQL for Business Intelligence and Analytics
This example-driven course provides thoughtful and interactive commentary throughout. We understand the common mistakes and misconceptions …
video
SQL Fundamentals for Business Intelligence
Many of us know a bit of SQL. But when it comes to answering real-world business …