book

SQL for Data Analytics

by Upom Malik, Matt Goldwasser, Benjamin Johnston

August 2019

Beginner to intermediate

386 pages

7h 15m

English

Packt Publishing

Read now

Unlock full access

About the BookAbout the AuthorsLearning ObjectivesAudienceApproachHardware RequirementsSoftware RequirementsConventionsInstallation and SetupInstalling PostgreSQL 10.9Installing PythonInstalling GitLoading the Sample DatabasesRunning SQL FilesAdditional Resources
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
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
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
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
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
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
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
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

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
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

5. Window Functions for Data Analysis

Learning Objectives

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

Explain what a window function is
Write basic window functions
Use common window functions to calculate statistics
Analyze sales data using window functions and a window frame

In this chapter, we will cover window functions, functions similar to an aggregate function but that allow a new range of capabilities and insights.

Introduction

In the previous chapter, we discussed aggregate functions, functions that can take a large group of rows and output a single value for them. Often, being able to summarize a group of rows to a single value is important and useful. However, there are times when you want to keep the individual rows ...