book

SQL for Data Analytics

Name: SQL for Data Analytics
ISBN: 9781789807356

by Upom Malik, Matt Goldwasser, Benjamin Johnston

August 2019

Beginner to intermediate

386 pages

7h 15m

English

Packt Publishing

Read now

Unlock full access

SQL for Data Analytics
Preface
About the BookAbout the AuthorsLearning ObjectivesAudienceApproachHardware RequirementsSoftware RequirementsConventionsInstallation and SetupInstalling PostgreSQL 10.9Installing PythonInstalling GitLoading the Sample DatabasesRunning SQL FilesAdditional Resources
1. Understanding and Describing Data
IntroductionThe World of DataTypes of DataData Analytics and StatisticsTypes of StatisticsActivity 1: Classifying a New DatasetMethods of Descriptive StatisticsUnivariate AnalysisData Frequency DistributionExercise 1: Creating a HistogramExercise 2: Calculating the Quartiles for Add-on SalesCentral TendencyExercise 3: Calculating the Central Tendency of Add-on SalesDispersionExercise 4: Dispersion of Add-on SalesBivariate AnalysisScatterplotsExercise 5: Calculating the Pearson Correlation Coefficient for Two VariablesActivity 2: Exploring Dealership Sales DataWorking with Missing DataStatistical Significance TestingCommon Statistical Significance TestsSummary
2. The Basics of SQL for Analytics
IntroductionRelational Databases and SQLAdvantages and Disadvantages of SQL DatabasesBasic Data Types of SQLNumericCharacterBooleanDatetimeData Structures: JSON and ArraysReading Tables: The SELECT QueryBasic Anatomy and Working of a SELECT QueryBasic Keywords in a SELECT QueryExercise 6: Querying the Salespeople Table Using Basic Keywords in a SELECT QueryActivity 3: Querying the customers Table Using Basic Keywords in a SELECT QueryCreating TablesCreating Blank TablesExercise 7: Creating a Table in SQLCreating Tables with SELECTUpdating TablesAdding and Removing ColumnsAdding New DataUpdating Existing RowsExercise 8: Updating Tables in SQLDeleting Data and TablesDeleting Values from a RowDeleting Rows from a TableDeleting TablesExercise 9: Unnecessary Reference TableActivity 4: Marketing OperationsSQL and AnalyticsSummary
3. SQL for Data Preparation
IntroductionAssembling DataConnecting Tables Using JOINTypes of JoinsExercise 10: Using Joins to Analyze Sales DealershipSubqueriesUnionsExercise 11: Generating an Elite Customer Party Guest List using UNIONCommon Table ExpressionsTransforming DataExercise 12: Using the CASE WHEN Function to Get Regional ListsActivity 5: Building a Sales Model Using SQL TechniquesSummary
4. Aggregate Functions for Data Analysis
IntroductionAggregate FunctionsExercise 13: Using Aggregate Functions to Analyze DataAggregate Functions with GROUP BYGROUP BY Multiple Column GROUP BY Exercise 14: Calculating the Cost by Product Type Using GROUP BYGrouping SetsOrdered Set AggregatesThe HAVING ClauseExercise 15: Calculating and Displaying Data Using the HAVING ClauseUsing Aggregates to Clean Data and Examine Data QualityFinding Missing Values with GROUP BYMeasuring Data Quality with AggregatesActivity 6: Analyzing Sales Data Using Aggregate FunctionsSummary
5. Window Functions for Data Analysis
IntroductionWindow FunctionsThe Basics of Window FunctionsExercise 16: Analyzing Customer Data Fill Rates over TimeThe WINDOW KeywordStatistics with Window FunctionsExercise 17: Rank Order of HiringWindow FrameExercise 18: Team Lunch MotivationActivity 7: Analyzing Sales Using Window Frames and Window FunctionsSummary
6. Importing and Exporting Data
IntroductionThe COPY CommandGetting Started with COPYCopying Data with psqlConfiguring COPY and \copyUsing COPY and \copy to Bulk Upload Data to Your DatabaseExercise 19: Exporting Data to a File for Further Processing in ExcelUsing R with Our DatabaseWhy Use R?Getting Started with RUsing Python with Our DatabaseWhy Use Python?Getting Started with PythonExercise 20: Exporting Data from a Database within Python Improving Postgres Access in Python with SQLAlchemy and PandasWhat is SQLAlchemy?Using Python with Jupyter NotebooksReading and Writing to our Database with PandasPerforming Data Visualization with PandasExercise 21: Reading Data and Visualizing Data in PythonWriting Data to the Database Using PythonImproving Python Write Speed with COPYReading and Writing CSV Files with PythonBest Practices for Importing and Exporting DataGoing Password-LessActivity 8: Using an External Dataset to Discover Sales TrendsSummary
7. Analytics Using Complex Data Types
IntroductionDate and Time Data Types for AnalysisStarting with the Date TypeTransforming Date TypesIntervalsExercise 22: Analytics with Time Series DataPerforming Geospatial Analysis in PostgresLatitude and LongitudeRepresenting Latitude and Longitude in PostgresExercise 23: Geospatial AnalysisUsing Array Data Types in PostgresStarting with ArraysUsing JSON Data Types in PostgresJSONB: Pre-Parsed JSONAccessing Data from a JSON or JSONB FieldCreating and Modifying Data in a JSONB FieldExercise 24: Searching through JSONBText Analytics Using PostgresTokenizing TextExercise 25: Performing Text AnalyticsPerforming Text SearchOptimizing Text Search on PostgresActivity 9: Sales Search and AnalysisSummary
8. Performant SQL
IntroductionDatabase Scanning MethodsQuery PlanningScanning and Sequential ScansExercise 26: Interpreting the Query PlannerActivity 10: Query PlanningIndex ScanningThe B-tree IndexExercise 27: Creating an Index ScanActivity 11: Implementing Index ScansHash IndexExercise 28: Generating Several Hash Indexes to Investigate PerformanceActivity 12: Implementing Hash IndexesEffective Index UsePerformant JoinsExercise 29: Determining the Use of Inner JoinsActivity 13: Implementing JoinsFunctions and TriggersFunction DefinitionsExercise 30: Creating Functions without ArgumentsActivity 14: Defining a Maximum Sale FunctionExercise 31: Creating Functions with Arguments Using a Single FunctionActivity 15: Creating Functions with ArgumentsTriggersExercise 32: Creating Triggers to Update FieldsActivity 16: Creating a Trigger to Track Average PurchasesKilling QueriesExercise 33: Canceling a Long QueryActivity 17: Terminating a Long QuerySummary

9. Using SQL to Uncover the Truth – a Case Study
IntroductionCase StudyScientific MethodExercise 34: Preliminary Data Collection Using SQL TechniquesExercise 35: Extracting the Sales InformationActivity 18: Quantifying the Sales DropExercise 36: Launch Timing AnalysisActivity 19: Analyzing the Difference in the Sales Price HypothesisExercise 37: Analyzing Sales Growth by Email Opening RateExercise 38: Analyzing the Performance of the Email Marketing CampaignConclusionsIn-Field TestingSummary
Appendix
1. Understanding and Describing DataActivity 1: Classifying a New DatasetActivity 2: Exploring Dealership Sales Data2. The Basics of SQL for AnalyticsActivity 3: Querying the customers Table Using Basic Keywords in a SELECT QueryActivity 4: Marketing Operations3. SQL for Data PreparationActivity 5: Building a Sales Model Using SQL Techniques4. Aggregate Functions for Data AnalysisActivity 6: Analyzing Sales Data Using Aggregate Functions5. Window Functions for Data AnalysisActivity 7: Analyzing Sales Using Window Frames and Window Functions6. Importing and Exporting DataActivity 8: Using an External Dataset to Discover Sales Trends7. Analytics Using Complex Data TypesActivity 9: Sales Search and Analysis8. Performant SQLActivity 10: Query PlanningActivity 11: Implementing Index ScansActivity 12: Implementing Hash IndexesActivity 13: Implementing JoinsActivity 14: Defining a Maximum Sale FunctionActivity 15: Creating Functions with ArgumentsActivity 16: Creating a Trigger to Track Average PurchasesActivity 17: Terminating a Long Query9. Using SQL to Uncover the Truth – a Case StudyActivity 18: Quantifying the Sales DropActivity 19: Analyzing the Difference in the Sales Price Hypothesis

Content preview from SQL for Data Analytics

3. SQL for Data Preparation

Learning Objectives

By the end of this chapter, you will be able to:

Assemble multiple tables and queries together into a dataset
Transform and clean data using SQL functions
Remove duplicate data using DISTINCT and DISTINCT ON

In this chapter, we will learn to clean and prepare our data for analysis using SQL techniques.

Introduction

In the previous chapter, we discussed the basics of SQL and how to work with individual tables in SQL. We also used CRUD (create, read, update and delete) operations on a table. These tables are the foundation for all the work undertaken in analytics. One of the first tasks implemented in analytics is to create clean datasets. According to Forbes, it is estimated that, almost ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781789807356

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design