Puzzleshot #004 - Joining data using Unix tools

raw data→unix tools→result

You’ve received a new request - the marketing department wants to validate the effectiveness of each dealer (ie, how many sales per dealer). Unfortunately the current database system is under maintenance and you can’t get access to the normal query tools.

Fortunately, as part of a testing process, you have TSV exports of both the dealer and orders tables.

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

You don’t have access to modern data tools, Excel, etc. You do have access to the usual Unix toolset (awk, sort, cut, etc). With just these tools, how would you answer the following:

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

This data is fairly straightforward - 500-1000 dealers and 20000 sales. Consider how you would answer these questions at this scale, but also consider what would change with a million rows of sales or 25000 dealers.

Reveal solution

The core challenge here is joining data across two files without modern tools. The key is the FNR==NR pattern in awk.

FNR==NR explained: awk has two row counters — NR counts rows across all files and never resets. FNR counts rows per file and resets at each new file. So FNR==NR is only true while reading the first file — a clean way to separate a loading phase from a processing phase.

The solution:

awk -F'\t' '
  FNR==NR {dealer[$1]=$2; next}
  NR>1 {total[$3]+=$4; count[$3]++}
  END {for (d in total) print count[d], total[d], dealer[d]}
' dealers.tsv orders.tsv | sort -rn

Load dealers into memory first, stream orders second, aggregate both count and dollar value in a single pass. File order matters — always load the smaller lookup file first.

Did you spot dealer_id 1999? It appears in orders but not dealers. Always handle unmatched records explicitly — silent data loss is worse than a noisy warning.

At scale: this approach works until dealers.tsv outgrows available RAM. Next Puzzleshot we start looking at how modern tools handle this differently.