Exploratory Data Analysis in SQL
Learn to profile a PostgreSQL database with SQL, summarizing numeric columns, cleaning messy text categories, and analyzing dates and timestamps.
Exploring an unfamiliar PostgreSQL database starts with basic questions: which tables matter, how they connect, and whether the values can be trusted. Exploratory data analysis in SQL answers those questions inside the database, with no export step. Working with Stack Overflow data, Fortune 500 company figures, and 311 help requests submitted to the city of Evanston, IL, you profile numeric, character, and date/time columns, catch errors and inconsistencies, and clean up messy values before analysis.
What You Will Achieve
- Work out how the tables in an unfamiliar database relate before writing analysis queries
- Judge the spread of a numeric column and spot anomalies using variance, percentile, and correlation functions
- Turn inconsistent free-text entries into standardized categories you can count and aggregate
- Spot periods with no records in a time series and measure how long requests take to complete
Program Overview
Tables, Keys, and Missing Values
Getting oriented in a new database and checking how complete its columns are and how its tables are linked.
- Table sizes and missing-value counts
- Foreign keys and entity relationship diagrams
- Column types, constraints, and casting
- Coalescing values
Summarizing Numeric Data and Temporary Tables
Summary statistics for numeric columns, plus temporary tables that hold intermediate results for longer queries.
- Division, truncation, and rounding
- Group statistics and distributions with generate_series
- Correlation, mean, and median
- Creating and inserting into temporary tables
Messy Text and Date/Time Data
Cleaning character data and analyzing timestamps, using the help requests submitted to Evanston, IL.
- Case, spacing, trimming, and delimiters
- Recoding categories and indicator variables
- ISO 8601 formats, date parts, and date arithmetic
- Date truncation, missing dates, and gaps between events
Certificate Awarded (Paid)
Complete all the course lessons to earn it.
Job Outlook
Exploring and cleaning data inside a database is routine work in data science, data engineering, analytics, and business intelligence, wherever SQL queries feed reports and further analysis.
Good for: Analysts who query PostgreSQL and want a systematic way to profile an unfamiliar database and its messy columns before analyzing it.
Keep in mind: The four chapters center on numeric, text, and date/time exploration of prepared datasets; full-text search and extensions are not among the lessons.
Bottom line: Its strength is the cleaning work, recoding messy text categories and finding date gaps, practiced on Stack Overflow, Fortune 500, and Evanston city data.
Frequently Asked Questions
Which datasets do the exercises use?
The exercises draw on Stack Overflow data, figures on Fortune 500 companies, and 311 help requests submitted to the city of Evanston, IL. The text and date chapters work mainly with those help requests.
Which database system are the queries written for?
The queries are written for PostgreSQL, and functions such as generate_series and date truncation are taught as PostgreSQL features, so you can go on to explore your own PostgreSQL databases.
How are temporary tables used in the lessons?
Temporary tables store intermediate results so a complex query can be broken into simpler steps. The text chapter also uses one to recode messy categorical values into standardized groups you can count.
Are video transcripts and a glossary available?
Yes. Each video has a live transcript you can reveal with the Show transcript option at the bottom left, and a course glossary sits in the resources section beside the lessons.


There are no reviews yet.