SQL for Data Analytics by Malik, Upom -- Read -- Imperial Library of Trantor

Index

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

← Prev
Back
Next →

← Prev
Back
Next →