Home Page Icon
Home Page
Table of Contents for
cover
Close
cover
by Upom Malik, Matt Goldwasser, Benjamin Johnston
SQL for Data Analytics
Preface
About the Book
About the Authors
Learning Objectives
Audience
Approach
Hardware Requirements
Software Requirements
Conventions
Installation and Setup
Installing PostgreSQL 10.9
Installing Python
Installing Git
Loading the Sample Databases
Running SQL Files
Additional Resources
Chapter 1
Understanding and Describing Data
Introduction
The World of Data
Types of Data
Data Analytics and Statistics
Types of Statistics
Activity 1: Classifying a New Dataset
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
Common Statistical Significance Tests
Summary
Chapter 2
The Basics of SQL for Analytics
Introduction
Relational Databases and SQL
Advantages and Disadvantages of SQL Databases
Basic Data Types of SQL
Numeric
Character
Boolean
Datetime
Data Structures: JSON and Arrays
Reading Tables: The SELECT Query
Basic Anatomy and Working of a SELECT Query
Basic Keywords in a SELECT Query
Exercise 6: Querying the Salespeople Table Using Basic Keywords in a SELECT Query
Activity 3: Querying the customers Table Using Basic Keywords in a SELECT Query
Creating Tables
Creating Blank Tables
Exercise 7: Creating a Table in SQL
Creating Tables with SELECT
Updating Tables
Adding and Removing Columns
Adding New Data
Updating Existing Rows
Exercise 8: Updating Tables in SQL
Deleting Data and Tables
Deleting Values from a Row
Deleting Rows from a Table
Deleting Tables
Exercise 9: Unnecessary Reference Table
Activity 4: Marketing Operations
SQL and Analytics
Summary
Chapter 3
SQL for Data Preparation
Introduction
Assembling Data
Connecting Tables Using JOIN
Types of Joins
Exercise 10: Using Joins to Analyze Sales Dealership
Subqueries
Unions
Exercise 11: Generating an Elite Customer Party Guest List using UNION
Common Table Expressions
Transforming Data
Exercise 12: Using the CASE WHEN Function to Get Regional Lists
Activity 5: Building a Sales Model Using SQL Techniques
Summary
Chapter 4
Aggregate Functions for Data Analysis
Introduction
Aggregate Functions
Exercise 13: Using Aggregate Functions to Analyze Data
Aggregate Functions with GROUP BY
GROUP BY
Multiple Column GROUP BY
Exercise 14: Calculating the Cost by Product Type Using GROUP BY
Grouping Sets
Ordered Set Aggregates
The HAVING Clause
Exercise 15: 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 Quality with Aggregates
Activity 6: Analyzing Sales Data Using Aggregate Functions
Summary
Chapter 5
Window Functions for Data Analysis
Introduction
Window Functions
The Basics of Window Functions
Exercise 16: Analyzing Customer Data Fill Rates over Time
The WINDOW Keyword
Statistics with Window Functions
Exercise 17: Rank Order of Hiring
Window Frame
Exercise 18: Team Lunch Motivation
Activity 7: Analyzing Sales Using Window Frames and Window Functions
Summary
Chapter 6
Importing and Exporting Data
Introduction
The COPY Command
Getting Started with COPY
Copying Data with psql
Configuring COPY and copy
Using COPY and copy to Bulk Upload Data to Your Database
Exercise 19: Exporting Data to a File for Further Processing in Excel
Using R with Our Database
Why Use R?
Getting Started with R
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
Going Password-Less
Activity 8: Using an External Dataset to Discover Sales Trends
Summary
Chapter 7
Analytics Using Complex Data Types
Introduction
Date and Time Data Types for Analysis
Starting with the Date Type
Transforming Date Types
Intervals
Exercise 22: Analytics with Time Series Data
Performing Geospatial Analysis in Postgres
Latitude and Longitude
Representing Latitude and Longitude in Postgres
Exercise 23: Geospatial Analysis
Using Array Data Types in Postgres
Starting with Arrays
Using JSON Data Types in Postgres
JSONB: Pre-Parsed JSON
Accessing Data from a JSON or JSONB Field
Creating and Modifying Data in a JSONB Field
Exercise 24: Searching through JSONB
Text Analytics Using Postgres
Tokenizing Text
Exercise 25: Performing Text Analytics
Performing Text Search
Optimizing Text Search on Postgres
Activity 9: Sales Search and Analysis
Summary
Chapter 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
Exercise 29: Determining the Use of Inner Joins
Activity 13: Implementing 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
Chapter 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
Chapter 1: Understanding and Describing Data
Activity 1: Classifying a New Dataset
Activity 2: Exploring Dealership Sales Data
Chapter 2: The Basics of SQL for Analytics
Activity 3: Querying the customers Table Using Basic Keywords in a SELECT Query
Activity 4: Marketing Operations
Chapter 3: SQL for Data Preparation
Activity 5: Building a Sales Model Using SQL Techniques
Chapter 4: Aggregate Functions for Data Analysis
Activity 6: Analyzing Sales Data Using Aggregate Functions
Chapter 5: Window Functions for Data Analysis
Activity 7: Analyzing Sales Using Window Frames and Window Functions
Chapter 6: Importing and Exporting Data
Activity 8: Using an External Dataset to Discover Sales Trends
Chapter 7: Analytics Using Complex Data Types
Activity 9: Sales Search and Analysis
Chapter 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
Chapter 9: Using SQL to Uncover the Truth – a Case Study
Activity 18: Quantifying the Sales Drop
Activity 19: Analyzing the Difference in the Sales Price Hypothesis
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
C11861_FM_ePub_Final_SZ
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset