book

SQL for Data Analytics - Third Edition

by Jun Shan, Matt Goldwasser, Upom Malik, Benjamin Johnston

August 2022

Beginner to intermediate

540 pages

9h 48m

English

Packt Publishing

Read now

Unlock full access

Third edition
About the BookAbout the AuthorsAudienceAbout the ChaptersConventionsSetting up Your EnvironmentInstalling PostgreSQL 14Downloading and Installing PostgreSQL on WindowsSetting the PATH VariableInstallation on macOSInstalling PythonInstalling Python on WindowsInstalling Python on LinuxInstalling Python on macOSInstalling GitInstalling Git on Windows or macOSInstalling Git on LinuxLoading the Sample Datasets – WindowsLoading the Sample Datasets – LinuxLoading the Sample Datasets – macOSRunning SQL filesAccessing the Code Files
IntroductionData Analytics and StatisticsActivity 1.01: Classifying a New DatasetTypes of Statistics Methods of Descriptive StatisticsUnivariate AnalysisData Frequency DistributionExercise 1.01: Creating a HistogramQuantilesExercise 1.02: Calculating the Quartiles for Add-On SalesCentral TendencyExercise 1.03: Calculating the Central Tendency of Add-On SalesDispersionExercise 1.04: Dispersion of Add-On SalesBivariate AnalysisScatterplotsLinear Trend Analysis and Pearson Correlation CoefficientExercise 1.05: Calculating the Pearson Correlation Coefficient for Two VariablesInterpreting and Analyzing the Correlation CoefficientTime Series DataActivity 1.02: Exploring Dealership Sales DataWorking with Missing DataStatistical Significance TestingCommon Statistical Significance TestsSQL and AnalyticsSummary
IntroductionThe World of DataTypes of DataRelational Databases and SQLAdvantages and Disadvantages of SQL DatabasesPostgreSQL Relational Database Management System (RDBMS)Exercise 2.01: Running Your First SELECT QuerySELECT StatementThe WHERE ClauseThe AND/OR ClauseThe IN/NOT IN ClauseORDER BY ClauseThe LIMIT ClauseIS NULL/IS NOT NULL ClauseExercise 2.02: Querying the salespeople Table Using Basic Keywords in a SELECT QueryActivity 2.01: Querying the customers Table Using Basic Keywords in a SELECT QueryCreating TablesCreating Blank TablesBasic Data Types of SQLNumericCharacterBooleanDatetimeData Structures: JSON and ArraysColumn ConstraintsSimple CREATE StatementExercise 2.03: Creating a Table in SQLCreating Tables with SELECTUpdating TablesAdding and Removing ColumnsAdding New DataUpdating Existing RowsExercise 2.04: Updating the Table to Increase the Price of a VehicleDeleting Data and TablesDeleting Values from a RowDeleting Rows from a TableDeleting TablesExercise 2.05: Deleting an Unnecessary Reference TableActivity 2.02: Creating and Modifying Tables for Marketing OperationsSQL and AnalyticsSummary
IntroductionAssembling DataConnecting Tables Using JOINTypes of JoinsInner JoinsOuter JoinsCross JoinsExercise 3.01: Using Joins to Analyze a Sales DealershipSubqueriesUnionsExercise 3.02: Generating an Elite Customer Party Guest List Using UNIONCommon Table ExpressionsCleaning DataThe CASE WHEN FunctionExercise 3.03: Using the CASE WHEN Function to Get Regional ListsThe COALESCE FunctionThe NULLIF FunctionThe LEAST/GREATEST FunctionsThe Casting FunctionTransforming DataThe DISTINCT and DISTINCT ON FunctionsActivity 3.01: Building a Sales Model Using SQL TechniquesSummary
IntroductionAggregate FunctionsExercise 4.01: Using Aggregate Functions to Analyze DataAggregate Functions with the GROUP BY ClauseThe GROUP BY ClauseMultiple-Column GROUP BYExercise 4.02: Calculating the Cost by Product Type Using GROUP BYGrouping SetsOrdered Set AggregatesAggregate Functions with the HAVING ClauseExercise 4.03: Calculating and Displaying Data Using the HAVING ClauseUsing Aggregates to Clean Data and Examine Data QualityFinding Missing Values with GROUP BYMeasuring Data Uniqueness with AggregatesActivity 4.01: Analyzing Sales Data Using Aggregate FunctionsSummary
IntroductionWindow FunctionsThe Basics of Window FunctionsExercise 5.01: Analyzing Customer Data Fill Rates over TimeThe WINDOW KeywordStatistics with Window FunctionsExercise 5.02: Rank Order of HiringWindow FrameExercise 5.03: Team Lunch MotivationActivity 5.01: Analyzing Sales Using Window Frames and Window FunctionsSummary
IntroductionThe COPY CommandRunning the psql CommandThe COPY Statement\COPY with psqlCreating Temporary ViewsConfiguring COPY and \COPYUsing COPY and \COPY to Bulk Upload Data to Your DatabaseExercise 6.01: Exporting Data to a File for Further Processing in ExcelUsing Python with your DatabaseGetting Started with PythonImproving PostgreSQL Access in Python with SQLAlchemy and pandasWhat is SQLAlchemy?Using Python with SQLAlchemy and pandasReading and Writing to a Database with pandasWriting Data to the Database Using PythonExercise 6.02: Reading, Visualizing, and Saving Data in PythonImproving Python Write Speed with COPYReading and Writing CSV Files with PythonBest Practices for Importing and Exporting DataGoing PasswordlessActivity 6.01: Using an External Dataset to Discover Sales TrendsSummary
IntroductionDate and Time Data types for AnalysisThe DATE Data typeTransforming Date Data typesIntervalsExercise 7.01: Analytics with Time Series DataPerforming Geospatial Analysis in PostgreSQLLatitude and LongitudeRepresenting Latitude and Longitude in PostgreSQLExercise 7.02: Geospatial AnalysisUsing Array Data types in PostgreSQLStarting with ArraysExercise 7.03: Analyzing Sequences Using ArraysUsing JSON Data types in PostgreSQLJSONB: Pre-Parsed JSONAccessing Data from a JSON or JSONB FieldLeveraging the JSON Path Language for JSONB FieldsCreating and Modifying Data in a JSONB FieldExercise 7.04: Searching through JSONBText Analytics Using PostgreSQLTokenizing TextExercise 7.05: Performing Text AnalyticsPerforming Text SearchOptimizing Text Search on PostgreSQLActivity 7.01: Sales Search and AnalysisSummary
IntroductionThe Importance of Highly Efficient SQLDatabase Scanning MethodsQuery PlanningExercise 8.01: Interpreting the Query PlannerActivity 8.01: Query PlanningIndex ScanningThe B-Tree IndexExercise 8.02: Creating an Index ScanActivity 8.02: Implementing Index ScansThe Hash IndexExercise 8.03: Generating Several Hash Indexes to Investigate PerformanceActivity 8.03: Implementing Hash IndexesEffective Index UseKilling QueriesExercise 8.04: Canceling a Long-Running QueryFunctions and TriggersFunction DefinitionsExercise 8.05: Creating Functions without ArgumentsActivity 8.04: Defining a Largest Sale Value FunctionExercise 8.06: Creating Functions with ArgumentsThe \df and \sf commandsActivity 8.05: Creating Functions with ArgumentsTriggersExercise 8.07: Creating Triggers to Update FieldsActivity 8.06: Creating a Trigger to Track Average PurchasesSummary

IntroductionCase StudyThe Scientific MethodExercise 9.01: Preliminary Data Collection Using SQL TechniquesExercise 9.02: Extracting the Sales InformationActivity 9.01: Quantifying the Sales DropExercise 9.03: Launch Timing AnalysisActivity 9.02: Analyzing the Difference in the Sales Price HypothesisExercise 9.04: Analyzing Sales Growth by Email Opening RateExercise 9.05: Analyzing the Performance of the Email Marketing CampaignConclusionsIn-Field TestingSummary
1. Understanding and Describing DataActivity 1.01: Classifying a New DatasetActivity 1.02: Exploring Dealership Sales Data2. The Basics of SQL for AnalyticsActivity 2.01: Querying the customers Table Using Basic Keywords in a SELECT QueryActivity 2.02: Creating and Modifying Tables for Marketing Operations 3. SQL for Data PreparationActivity 3.01: Building a Sales Model Using SQL Techniques4. Aggregate Functions for Data AnalysisActivity 4.01: Analyzing Sales Data Using Aggregate Functions5. Window Functions for Data AnalysisActivity 5.01: Analyzing Sales Using Window Frames and Window Functions6. Importing and Exporting DataActivity 6.01: Using an External Dataset to Discover Sales Trends7. Analytics Using Complex Data Types Activity 7.01: Sales Search and Analysis8. Performant SQLActivity 8.01: Query PlanningActivity 8.02: Implementing Index ScansActivity 8.03: Implementing Hash IndexesActivity 8.04: Defining a Largest Sale Value FunctionActivity 8.05: Creating Functions with ArgumentsActivity 8.06: Creating a Trigger to Track Average Purchases9. Using SQL to Uncover the Truth: A Case Study Activity 9.01: Quantifying the Sales DropActivity 9.02: Analyzing the Difference in the Sales Price Hypothesis

Content preview from SQL for Data Analytics - Third Edition

5. Window Functions for Data Analysis

Overview

In this chapter, you will learn the conceptual logic of window functions, write SQL to execute these functions, and modify them using keywords such as PARTITION BY and ORDER BY.

By the end of this chapter, you will be able to apply these functions to gain new insights into data and understand the properties of datasets, such as ranking and percentiles.

Introduction

You have learned simple functions such as CASE WHEN, COALESCE, and NULLIF in Chapter 3, SQL for Data Preparation. These functions receive data from a single row and produce a result for this row. The result of these functions is only determined by the data value in the row and has nothing to do with the dataset it is in. You have ...

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.

Start your free trial

Publisher Resources

ISBN: 9781801812870Supplemental Content

SQL for Data Analytics - Third Edition

by Jun Shan, Matt Goldwasser, Upom Malik, Benjamin Johnston

5. Window Functions for Data Analysis

Introduction

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.

You might also like

Master SQL for Data Analysis

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

Analytics Engineering with SQL and dbt

SQL for Data Analysis

Publisher Resources

5. Window Functions for Data Analysis

Introduction

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.

You might also like

Master SQL for Data Analysis

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

Analytics Engineering with SQL and dbt

SQL for Data Analysis

Publisher Resources

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.