Excel
Checking Totals Between Two Excel Reports With SQL
Reconcile two Excel reports with SQL: compare grand totals, then group totals, then rows with a full outer join, and sort the differences into causes.
7 min read
Topic
Data Analysis
Answers from raw rows: aggregates, cohorts, funnels, time series, plus the string and text wrangling that precedes every report.
Excel
Building Cohort Tables From an Excel Export With SQL
Turn an Excel export of orders into a cohort retention table with SQL: first purchase month, months since, a retention triangle and honest recent cohorts.
7 min read
Excel
Building a Forecast Baseline From Excel History in SQL
Build simple forecast baselines from monthly history in SQL: last value, seasonal naive, moving average and linear trend, and test them on held out months.
7 min read
Excel
Finding Duplicates and Gaps in an Excel Sheet With SQL
Use SQL on an Excel sheet to find repeated keys, missing numbers in a sequence and missing dates with GROUP BY, window functions and gaps and islands.
7 min read
Excel
Year Over Year Comparison From a Monthly Excel Sheet
Compare each month with the same month last year from an Excel sheet in SQL: LAG, self joins, partial years, missing months and percent change.
7 min read
Excel
Summarizing a Sales Workbook With GROUP BY in SQL Queries
Summarize a sales workbook with GROUP BY: totals by region and month, averages, rollups and ranking, with checks that make the numbers trustworthy.
7 min read
Excel
Joining Two Excel Sheets With SQL in DuckDB or SQLite
Join two Excel sheets with SQL in DuckDB or SQLite: choose the join type, find rows that match nothing, avoid duplicated keys and check row counts.
7 min read
Excel
Querying an Excel File With SQL in DuckDB Step by Step
Run SQL on an Excel workbook with DuckDB: the excel extension, a pandas DataFrame route, sheet and range options, types and checks that keep numbers right.
7 min read
ExcelGuide
Doing Proper Analysis on Excel Data With SQL in Practice
A guide to analyzing Excel data with SQL: query workbooks in DuckDB, join, summarize, compare years, find gaps, build cohorts, reconcile and replace models.
16 min read
XML
Reading Government Open Data in XML With a USGS Feed
Read a real public QuakeML feed from the USGS: two namespaces, nested value elements, depth in meters, magnitude types, and checks that catch a wrong unit.
6 min read
CSV
Writing Query Results Back Out to CSV With DuckDB COPY
Export a DuckDB query to CSV with COPY. Control the delimiter, NULL text, quoting, date format and compression, and split output into one file per value.
9 min read
CSV
Joining Two CSV Files With SQL in DuckDB Without a Database
Join two CSV files on a key with plain SQL in DuckDB. Covers a 2M by 250k row join, keys that differ by case and spaces, left and anti joins, and type errors.
8 min read
Get new articles by email
We send one email when a new article is published. No spam.