book

SQL for Data Analysis

by Cathy Tanimura

September 2021

Beginner to intermediate

357 pages

9h 53m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Includes

Has Sandbox

Conventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
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
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
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
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
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
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
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
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
Funnel AnalysisChurn, Lapse, and Other Definitions of DepartureBasket AnalysisResourcesBooks and BlogsData SetsFinal Thoughts

Content preview from SQL for Data Analysis

Index

Symbols

! (exclamation point) negation operator in regular expressions, Regular Expressions
% (percent sign)
- modulo operator, Sampling with %, mod
- wildcard matching with LIKE, Wildcard Matches: LIKE, ILIKE
() (parentheses)
- enclosing expressions in regular expressions, Regular Expressions
- in SQL code, Capitalization, Indentation, Parentheses, and Other Formatting Tricks
- using to control order of operations, Wildcard Matches: LIKE, ILIKE
* (asterisk)
- matching multiple characters in regular expressions, Regular Expressions
- matching zero or more times in regular expressions, Regular Expressions
+ (plus sign)
- addition operator, Date Math
- concatenation operator, Date and Timestamp Format Conversions
- matching one or more times in regular expressions, Regular Expressions
- (dash), indicating range of characters in regular expressions, Regular Expressions
-- (double dash) single line comments in SQL, Commenting
. (period), matching any single character in regular expressions, Regular Expressions
/* */ comment for multiple lines of code, Commenting
:: (double colon) operator, Text Transformations
? (question mark), matching zero or more times in regular expressions, Regular Expressions
[] (brackets), enclosing character class in regular expressions, Regular Expressions
\ (backslash)
- escape character in pattern matching, Wildcard Matches: LIKE, ILIKE
- escape character in regular expressions, Regular Expressions
\A, matching beginning of string in regular expressions, Regular Expressions
\n line feed or newline ...

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

SQL for Data Analytics

Upom Malik, Matt Goldwasser, Benjamin Johnston

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

John L. Viescas

Analytics Engineering with SQL and dbt

Rui Pedro Machado, Helder Russa

Getting Started with SQL

Thomas Nield

Publisher Resources

ISBN: 9781492088776Errata Page Supplemental Content