book

SQL for Data Analysis

Name: SQL for Data Analysis
Author: Cathy Tanimura
ISBN: 9781492088783

by Cathy Tanimura

September 2021

Beginner to intermediate

357 pages

9h 53m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Includes

Sandbox

Preface
Conventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
1. Analysis with SQL
What Is Data Analysis?Why SQL?What Is SQL?Benefits of SQLSQL Versus R or PythonSQL as Part of the Data Analysis WorkflowDatabase Types and How to Work with ThemRow-Store DatabasesColumn-Store DatabasesOther Types of Data InfrastructureConclusion
2. Preparing Data for Analysis
Types of DataDatabase Data TypesStructured Versus UnstructuredQuantitative Versus Qualitative DataFirst-, Second-, and Third-Party DataSparse DataSQL Query StructureProfiling: DistributionsHistograms and FrequenciesBinningn-TilesProfiling: Data QualityDetecting DuplicatesDeduplication with GROUP BY and DISTINCTPreparing: Data CleaningCleaning Data with CASE TransformationsType Conversions and CastingDealing with Nulls: coalesce, nullif, nvl FunctionsMissing DataPreparing: Shaping DataFor Which Output: BI, Visualization, Statistics, MLPivoting with CASE StatementsUnpivoting with UNION Statementspivot and unpivot FunctionsConclusion
3. Time Series Analysis
Date, Datetime, and Time ManipulationsTime Zone ConversionsDate and Timestamp Format ConversionsDate MathTime MathJoining Data from Different SourcesThe Retail Sales Data SetTrending the DataSimple TrendsComparing ComponentsPercent of Total CalculationsIndexing to See Percent Change over TimeRolling Time WindowsCalculating Rolling Time WindowsRolling Time Windows with Sparse DataCalculating Cumulative ValuesAnalyzing with SeasonalityPeriod-over-Period Comparisons: YoY and MoMPeriod-over-Period Comparisons: Same Month Versus Last YearComparing to Multiple Prior PeriodsConclusion
4. Cohort Analysis
Cohorts: A Useful Analysis FrameworkThe Legislators Data SetRetentionSQL for a Basic Retention CurveAdjusting Time Series to Increase Retention AccuracyCohorts Derived from the Time Series ItselfDefining the Cohort from a Separate TableDealing with Sparse CohortsDefining Cohorts from Dates Other Than the First DateRelated Cohort AnalysesSurvivorshipReturnship, or Repeat Purchase BehaviorCumulative CalculationsCross-Section Analysis, Through a Cohort LensConclusion
5. Text Analysis
Why Text Analysis with SQL?What Is Text Analysis?Why SQL Is a Good Choice for Text AnalysisWhen SQL Is Not a Good ChoiceThe UFO Sightings Data SetText CharacteristicsText ParsingText TransformationsFinding Elements Within Larger Blocks of TextWildcard Matches: LIKE, ILIKEExact Matches: IN, NOT INRegular ExpressionsConstructing and Reshaping TextConcatenationReshaping TextConclusion
6. Anomaly Detection
Capabilities and Limits of SQL for Anomaly DetectionThe Data SetDetecting OutliersSorting to Find AnomaliesCalculating Percentiles and Standard Deviations to Find AnomaliesGraphing to Find Anomalies VisuallyForms of AnomaliesAnomalous ValuesAnomalous Counts or FrequenciesAnomalies from the Absence of DataHandling AnomaliesInvestigationRemovalReplacement with Alternate ValuesRescalingConclusion
7. Experiment Analysis
Strengths and Limits of Experiment Analysis with SQLThe Data SetTypes of ExperimentsExperiments with Binary Outcomes: The Chi-Squared TestExperiments with Continuous Outcomes: The t-TestChallenges with Experiments and Options for Rescuing Flawed ExperimentsVariant AssignmentOutliersTime BoxingRepeated Exposure ExperimentsWhen Controlled Experiments Aren’t Possible: Alternative AnalysesPre-/Post-AnalysisNatural Experiment AnalysisAnalysis of Populations Around a ThresholdConclusion
8. Creating Complex Data Sets for Analysis
When to Use SQL for Complex Data SetsAdvantages of Using SQLWhen to Build into ETL InsteadWhen to Put Logic in Other ToolsCode OrganizationCommentingCapitalization, Indentation, Parentheses, and Other Formatting TricksStoring CodeOrganizing ComputationsUnderstanding Order of SQL Clause EvaluationSubqueriesTemporary TablesCommon Table Expressionsgrouping setsManaging Data Set Size and Privacy ConcernsSampling with %, modReducing DimensionalityPII and Data PrivacyConclusion
9. Conclusion
Funnel AnalysisChurn, Lapse, and Other Definitions of DepartureBasket AnalysisResourcesBooks and BlogsData SetsFinal Thoughts

Index

Overview

With the explosion of data, computing power, and cloud data warehouses, SQL has become an even more indispensable tool for the savvy analyst or data scientist. This practical book reveals new and hidden ways to improve your SQL skills, solve problems, and make the most of SQL as part of your workflow.

You'll learn how to use both common and exotic SQL functions such as joins, window functions, subqueries, and regular expressions in new, innovative ways--as well as how to combine SQL techniques to accomplish your goals faster, with understandable code. If you work with SQL databases, this is a must-have reference.

Learn the key steps for preparing your data for analysis
Perform time series analysis using SQL's date and time manipulations
Use cohort analysis to investigate how groups change over time
Use SQL's powerful functions and operators for text analysis
Detect outliers in your data and replace them with alternate values
Establish causality using experiment analysis, also known as A/B testing

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: 9781492088776Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills