Capstone: your own file
Fourteen lessons of somebody else's furniture sales. This one has two halves. First, three questions that need most of the course at once and no new syntax at all. Then the part that matters: a file picker, so the last query you run here is against data you actually care about.
The three graded questions still run against the course sales file, because a grader cannot know what is in your spreadsheet. The editor below the exercises is yours.
What to do with a file you have never queried
There is a routine, and it is four queries long. I run it on every new file, and it takes under a minute.
One: look.
SELECT * FROM csv LIMIT 20. You
are reading the column names and the shape of the values, not the data. Are dates ISO or
American? Do numbers have currency symbols or thousands separators in them? Is there a header row
at all? Five seconds here saves twenty minutes of confused filtering.
Two: count.
SELECT COUNT(*) FROM csv, and
compare it to what you expected. Then count the distinct values of whatever you think the key is.
If those two numbers differ, the file has duplicates and every total you compute is suspect.
Three: find the gaps. Put
COUNT(*) beside
COUNT(column) for every column
you plan to use. The differences are your missing values, and knowing them before you average
anything is the difference between a number and a guess.
Four: list the categories.
SELECT DISTINCT status FROM csv
on each column you intend to filter or group by. This is where you discover the trailing spaces,
the two spellings of "Cancelled", and the placeholder value somebody typed in 2019.
Only then ask the real question. Nine times out of ten it is the same shape you have written a dozen times in this course: filter, group, aggregate, sort, limit.
What the file picker does
It reads the bytes in your browser and hands them to DuckDB in this tab. There is no upload, no
endpoint, no copy on a server, and you can verify that yourself: open your browser's network
panel before you choose the file and watch that nothing goes out. Your table is named after your
file, and it also answers to
csv, so
SELECT * FROM csv LIMIT 20 works
whatever you loaded.
Column types are sniffed from the values, which is usually right and occasionally not. A postal
code column full of digits may arrive as a number and lose its leading zeros; an ID that is
sometimes numeric and sometimes not may arrive as text. When a comparison behaves oddly, check
the type first, and cast rather than argue with it:
TRY_CAST(col AS DOUBLE) returns
null instead of failing on the rows that will not convert, which is also a neat way to find them.
The lesson page keeps things modest in size. A few megabytes is comfortable. Past that, the tools below are the right place.
Where to go from here
You now know enough SQL to answer most questions anyone asks of a flat file, and the grammar transfers unchanged to Postgres, BigQuery, Snowflake and Redshift. What you are missing is not more syntax; it is a bigger workbench.
The pipeline in the full editor is worth a paragraph on its own. Every operation you apply is a step you can reopen, edit and reorder, and any of them can be a raw SQL step. That turns a one-off query into something you can rerun next month against a fresh export, which is the difference between answering a question once and owning a report.
Exercises
1. Region by month
Return one row per month and region with the revenue total. Three columns: month (the first of the month), region, and total.
Hint
date_trunc from lesson 10, two grouping keys from lesson 9. Order does not matter here.
2. Month over month growth
Return month, total and the percentage change from the previous month as mom_pct, rounded to one decimal, oldest first. The first month's percentage is null. Order is checked.
Hint
A CTE for the monthly totals, then LAG over it twice: once in the numerator, once in the denominator.
3. The product scorecard
For products with more than twenty thousand in total revenue, return product, orders, units and revenue (rounded to two decimals), biggest revenue first. Order is checked.
Hint
One grouped query with three aggregates, a HAVING, an ORDER BY, and no CTE needed.
What to look up next
Two things, in this order. First, EXPLAIN: put the word
in front of any query and the engine shows you its plan. You do not need to understand all of it
to notice when a query is reading a table twice. Second,
the DuckDB CSV reader options, especially
read_csv with explicit
types and
dateformat. Sniffing is a
convenience; naming your types is how you make an import reproducible.
And then stop reading and go query something. The fastest way to consolidate all of this is one real question about one real file, answered properly, this week.