Puzzleshot #005 - Introducing DuckDB

raw data→duckdb→result

Over the last few Puzzleshots we’ve been working under constraint — basic unix tools, no modern tooling. Those skills are real and worth having. That said, constraints aren’t always the reality, so let’s introduce a tool you’re likely familiar with.

Enter DuckDB.

If you haven’t used it yet, DuckDB is a free, open-source analytical database that runs entirely in-process — no server, no setup, no cluster (though the brand new Quack protocol is changing that — DuckDB instances can now talk to each other over a network, which is a significant architectural shift). Just install it and start querying. It reads CSV, TSV, Parquet, and JSON directly without importing anything first.

That last part is worth pausing on:

SELECT * FROM 'orders.tsv' LIMIT 5;

That’s it. No import step, no database creation, no schema definition. DuckDB infers the schema and queries the file directly.

This week’s puzzle — same questions as #004:

Using the same dealers and orders files from Puzzleshot 004:

What dealer had the most quantity of sales?
Which dealer had the highest dollar value of sales?

But this time, use DuckDB. How does your approach change compared to the awk solution?

Bonus question: DuckDB can query multiple files with a single wildcard. If your orders were split across 500 files like in Puzzleshot 003, how would you query all of them at once?

Reveal solution

Ok, let’s take a look at a possible solution for Puzzleshot-005, using DuckDB to join TSV files.

A quick reminder of the format:

Dealers file:

dealer_id dealer_name region_id  
1001 Prairie Iron Equipment North  
1002 Sunbelt Tractor Co South  
1003 Midwest Farm Supply North  
1004 Western Ag Solutions West

Orders file:

order_id customer_id dealer_id order_total  
5001 8801 1001 124500.00  
5002 8802 1003 87250.00  
5003 8803 1001 203000.00  
5004 8804 1002 156750.00  
5005 8805 1999 94000.00

Now for the actual questions:

What dealer had the most quantity of sales?

This one we can solve with:

select 
  d.dealer_id, 
  d.dealer_name, 
  count(o.\*) as order_count
  from 'dealers.tsv' d left join 'orders.tsv' o
   on d.dealer_id = o.dealer_id
  order by count(o.\*) desc
  limit 1

Notice that we’ve been able to substitute the filenames as the names of the tables in question. If DuckDB recognizes the filetype, it will automatically use read_csv_auto (or read_parquet, or read_json, etc) to parse the file into memory automatically as though it were built into the database.

Given this is just the SQL portion, we’d actually run this from the command line.

duckdb -s "select d.dealer_id, d.dealer_name, count(o.\*) as order_count from 'dealers.tsv' d left join 'orders.tsv' o on d.dealer_id = o.dealer_id order by count(o.\*) desc limit 1"

It could also be run interactively in via the duckdb cli tool or with one of the many client libraries.

Which dealer had the highest dollar value of sales?

select \* from (
  select 
    d.dealer_id, 
    d.dealer_name, 
    sum(o.order_total) as sales_total
  from 'dealers.tsv' d left join 'orders.tsv' o
    on d.dealer_id = o.dealer_id
) 
order by sales_total desc
limit 1

For this one, we change our aggregate to using sum and wrap that in a subquery to order the data accordingly. Note that I am being a little more verbose than needed with DuckDB as it can usually handle sorting by aggregate aliases without issue, but other SQL databases do not.