SQL for Data Analytics: Harness the power of SQL to extract insights from data, 3rd Edition

Cover
FM
Copyright
Table of Contents
Preface
Chapter 1: Understanding and Describing Data
	Introduction
	Data Analytics and Statistics
		Activity 1.01: Classifying a New Dataset
	Types of Statistics
		Methods of Descriptive Statistics
		Univariate Analysis
			Data Frequency Distribution
		Exercise 1.01: Creating a Histogram
			Quantiles
		Exercise 1.02: Calculating the Quartiles for Add-On Sales
			Central Tendency
		Exercise 1.03: Calculating the Central Tendency of Add-On Sales
			Dispersion
		Exercise 1.04: Dispersion of Add-On Sales
		Bivariate Analysis
			Scatterplots
			Linear Trend Analysis and Pearson Correlation Coefficient
		Exercise 1.05: Calculating the Pearson Correlation Coefficient for Two Variables
		Interpreting and Analyzing the Correlation Coefficient
			Time Series Data
		Activity 1.02: Exploring Dealership Sales Data
	Working with Missing Data
	Statistical Significance Testing
		Common Statistical Significance Tests
	SQL and Analytics
	Summary
Chapter 2: The Basics of SQL for Analytics
	Introduction
	The World of Data
		Types of Data
	Relational Databases and SQL
		Advantages and Disadvantages of SQL Databases
	PostgreSQL Relational Database Management System (RDBMS)
		Exercise 2.01: Running Your First SELECT Query
		SELECT Statement
		The WHERE Clause
		The AND/OR Clause
		The IN/NOT IN Clause
		ORDER BY Clause
		The LIMIT Clause
		IS NULL/IS NOT NULL Clause
		Exercise 2.02: Querying the salespeople Table Using Basic Keywords in a SELECT Query
		Activity 2.01: Querying the customers Table Using Basic Keywords in a SELECT Query
	Creating Tables
		Creating Blank Tables
	Basic Data Types of SQL
		Numeric
		Character
		Boolean
		Datetime
	Data Structures: JSON and Arrays
	Column Constraints
		Simple CREATE Statement
		Exercise 2.03: Creating a Table in SQL
		Creating Tables with SELECT
	Updating Tables
		Adding and Removing Columns
		Adding New Data
		Updating Existing Rows
		Exercise 2.04: Updating the Table to Increase the Price of a Vehicle
		Deleting Data and Tables
		Deleting Values from a Row
		Deleting Rows from a Table
		Deleting Tables
		Exercise 2.05: Deleting an Unnecessary Reference Table
		Activity 2.02: Creating and Modifying Tables for Marketing Operations
	SQL and Analytics
	Summary
Chapter 3: SQL for Data Preparation
	Introduction
	Assembling Data
		Connecting Tables Using JOIN
		Types of Joins
			Inner Joins
			Outer Joins
			Cross Joins
		Exercise 3.01: Using Joins to Analyze a Sales Dealership
		Subqueries
		Unions
		Exercise 3.02: Generating an Elite Customer Party Guest List Using UNION
		Common Table Expressions
	Cleaning Data
		The CASE WHEN Function
		Exercise 3.03: Using the CASE WHEN Function to Get Regional Lists
		The COALESCE Function
		The NULLIF Function
		The LEAST/GREATEST Functions
		The Casting Function
	Transforming Data
		The DISTINCT and DISTINCT ON Functions
		Activity 3.01: Building a Sales Model Using SQL Techniques
	Summary
Chapter 4: Aggregate Functions for Data Analysis
	Introduction
	Aggregate Functions
		Exercise 4.01: Using Aggregate Functions to Analyze Data
	Aggregate Functions with the GROUP BY Clause
		The GROUP BY Clause
		Multiple-Column GROUP BY
		Exercise 4.02: Calculating the Cost by Product Type Using GROUP BY
		Grouping Sets
		Ordered Set Aggregates
	Aggregate Functions with the HAVING Clause
		Exercise 4.03: Calculating and Displaying Data Using the HAVING Clause
	Using Aggregates to Clean Data and Examine Data Quality
		Finding Missing Values with GROUP BY
		Measuring Data Uniqueness with Aggregates
		Activity 4.01: Analyzing Sales Data Using Aggregate Functions
	Summary
Chapter 5: Window Functions for Data Analysis
	Introduction
	Window Functions
		The Basics of Window Functions
		Exercise 5.01: Analyzing Customer Data Fill Rates over Time
		The WINDOW Keyword
	Statistics with Window Functions
		Exercise 5.02: Rank Order of Hiring
	Window Frame
		Exercise 5.03: Team Lunch Motivation
		Activity 5.01: Analyzing Sales Using Window Frames and Window Functions
	Summary
Chapter 6: Importing and Exporting Data
	Introduction
	The COPY Command
		Running the psql Command
		The COPY Statement
		\COPY with psql
		Creating Temporary Views
		Configuring COPY and \COPY
		Using COPY and \COPY to Bulk Upload Data to Your Database
		Exercise 6.01: Exporting Data to a File for Further Processing in Excel
	Using Python with your Database
		Getting Started with Python
		Improving PostgreSQL Access in Python with SQLAlchemy and pandas
		What is SQLAlchemy?
		Using Python with SQLAlchemy and pandas
		Reading and Writing to a Database with pandas
		Writing Data to the Database Using Python
		Exercise 6.02: Reading, Visualizing, and Saving Data in Python
		Improving Python Write Speed with COPY
		Reading and Writing CSV Files with Python
		Best Practices for Importing and Exporting Data
	Going Passwordless
		Activity 6.01: Using an External Dataset to Discover Sales Trends
	Summary
Chapter 7: Analytics Using Complex Data Types
	Introduction
	Date and Time Data types for Analysis
		The DATE Data type
		Transforming Date Data types
		Intervals
		Exercise 7.01: Analytics with Time Series Data
	Performing Geospatial Analysis in PostgreSQL
		Latitude and Longitude
		Representing Latitude and Longitude in PostgreSQL
		Exercise 7.02: Geospatial Analysis
	Using Array Data types in PostgreSQL
		Starting with Arrays
		Exercise 7.03: Analyzing Sequences Using Arrays
	Using JSON Data types in PostgreSQL
		JSONB: Pre-Parsed JSON
		Accessing Data from a JSON or JSONB Field
		Leveraging the JSON Path Language for JSONB Fields
		Creating and Modifying Data in a JSONB Field
		Exercise 7.04: Searching through JSONB
	Text Analytics Using PostgreSQL
		Tokenizing Text
		Exercise 7.05: Performing Text Analytics
		Performing Text Search
		Optimizing Text Search on PostgreSQL
		Activity 7.01: Sales Search and Analysis
	Summary
Chapter 8: Performant SQL
	Introduction
	The Importance of Highly Efficient SQL
	Database Scanning Methods
		Query Planning
		Exercise 8.01: Interpreting the Query Planner
		Activity 8.01: Query Planning
		Index Scanning
		The B-Tree Index
		Exercise 8.02: Creating an Index Scan
		Activity 8.02: Implementing Index Scans
		The Hash Index
		Exercise 8.03: Generating Several Hash Indexes to Investigate Performance
		Activity 8.03: Implementing Hash Indexes
		Effective Index Use
	Killing Queries
		Exercise 8.04: Canceling a Long-Running Query
	Functions and Triggers
		Function Definitions
		Exercise 8.05: Creating Functions without Arguments
		Activity 8.04: Defining a Largest Sale Value Function
		Exercise 8.06: Creating Functions with Arguments
			The \df and \sf commands
		Activity 8.05: Creating Functions with Arguments
		Triggers
		Exercise 8.07: Creating Triggers to Update Fields
		Activity 8.06: Creating a Trigger to Track Average Purchases
	Summary
Chapter 9: Using SQL to Uncover the Truth – a Case Study
	Introduction
	Case Study
		The Scientific Method
		Exercise 9.01: Preliminary Data Collection Using SQL Techniques
		Exercise 9.02: Extracting the Sales Information
		Activity 9.01: Quantifying the Sales Drop
		Exercise 9.03: Launch Timing Analysis
		Activity 9.02: Analyzing the Difference in the Sales Price Hypothesis
		Exercise 9.04: Analyzing Sales Growth by Email Opening Rate
		Exercise 9.05: Analyzing the Performance of the Email Marketing Campaign
		Conclusions
		In-Field Testing
	Summary
Appendix
Author Page
Index