Aggregating Crawl Stats with DuckDB SQL

With a parsed logs table in hand — built as shown in querying access logs with DuckDB — the crawl-budget questions collapse into a handful of GROUP BY statements. This page is the query cookbook: status distribution with in-query percentage shares, crawl allocation by site section, hour-of-day rhythm, and a day-over-day delta that shows whether a change moved the needle.

The value of doing this in SQL rather than a chain of sort | uniq -c is that shares, rankings, and comparisons that need multiple passes on the command line become one statement with a window function. It is the same reporting you would build in a Kibana crawl dashboard or with LogQL in Grafana Loki, without standing up a service.

Diagnosis: What "Wasted Crawl" Looks Like in Aggregate

A healthy crawl profile is dominated by 200 responses on canonical URLs. The symptom of waste is a large share of 3xx/4xx or a crawler concentrating on low-value sections. One query surfaces both:

SELECT status, count(*) AS hits,
       round(100.0*count(*)/sum(count(*)) OVER (),1) AS pct
FROM logs WHERE ua ILIKE '%googlebot%'
GROUP BY status ORDER BY hits DESC;

Expected Output: if non-200 rows sum to more than ~10–15%, budget is leaking into redirects and errors — the number to drive down.

Four crawl reports from one DuckDB table A single parsed logs table feeds four SQL aggregations: status distribution with percentage shares, crawl hits grouped by site section, hits grouped by hour of day, and a day-over-day delta comparing two dates. logs table typed columns status share (window fn) crawl by section hits by hour day-over-day delta crawl-budget report CSV / Parquet export

Concept: Window Functions Replace Multi-Pass Pipes

A share-of-total needs the grand total and each group's count at once. On the command line that is two passes; in SQL, sum(count(*)) OVER () computes the total across all groups in the same query, so every row carries its percentage. The same idea — an aggregate evaluated over a window rather than a GROUP BY bucket — powers rankings (rank() OVER (ORDER BY ...)) and running totals without a self-join.

Step-by-Step: Build the Report

Step 1: Crawl by section, with a waste flag. Group by the first path segment and mark low-value areas.

SELECT '/' || split_part(ltrim(path,'/'),'/',1) AS section,
       count(*) AS hits,
       sum(CASE WHEN status >= 300 THEN 1 ELSE 0 END) AS non_200
FROM logs WHERE ua ILIKE '%googlebot%'
GROUP BY section ORDER BY hits DESC LIMIT 10;

Expected Output: sections ranked by crawl volume with their non-200 counts, so a heavily crawled section that is mostly redirects jumps out.

Step 2: Hour-of-day rhythm. date_trunc buckets timestamps into hours.

SELECT hour(request_time) AS hod, count(*) AS hits
FROM logs WHERE ua ILIKE '%googlebot%'
GROUP BY hod ORDER BY hod;

Expected Output: 24 rows; a flat distribution is normal, a sharp spike can indicate a crawl surge worth correlating with a deploy.

Step 3: Day-over-day delta. Compare two dates in one query with conditional aggregation.

SELECT status,
       sum(CASE WHEN request_time::DATE = DATE '2026-06-18' THEN 1 ELSE 0 END) AS d1,
       sum(CASE WHEN request_time::DATE = DATE '2026-06-19' THEN 1 ELSE 0 END) AS d2
FROM logs WHERE ua ILIKE '%googlebot%'
GROUP BY status ORDER BY status;

Expected Output: per-status counts for both days side by side; a falling 301/404 count after a redirect fix is the confirmation you are looking for.

Edge-Case Handling

  • Mixed time zones across servers. Cast to a single zone before bucketing, or hour-of-day blurs. Normalize as in normalizing log timezones in Python at parse time.
  • Trailing-slash duplication in sections. split_part on the first segment sidesteps most of it, but normalize the full path with rtrim(path,'/') if you group deeper.

Verification

Confirm the shares sum to 100 and the section totals reconcile with the overall Googlebot count:

SELECT sum(hits) AS section_total,
       (SELECT count(*) FROM logs WHERE ua ILIKE '%googlebot%') AS googlebot_total
FROM (SELECT count(*) hits FROM logs WHERE ua ILIKE '%googlebot%'
      GROUP BY split_part(ltrim(path,'/'),'/',1));

Expected Output: section_total equals googlebot_total. A mismatch means a NULL path slipped through — tighten the parse.

Why SQL Aggregation Beats Command-Line Pipelines

The recipes on this page could all be expressed as chains of awk, sort, and uniq, so it is worth being clear about why SQL is the better tool once the questions get beyond the simplest. A command-line pipeline computes one aggregation per pass and requires a fresh pass — a re-read of the whole file — for each new question, so answering five questions means five reads and five hand-assembled pipelines. SQL, running over a parsed table, answers each question as a declarative query the engine optimizes, and it expresses in one clause things that take multiple pipeline stages or a self-join to do with command-line tools: a percentage-of-total needs the grand total and each group's count together, which a window function computes in one pass but a pipeline needs two.

The gap widens as the analysis gets richer. A day-over-day comparison, a ranking with in-query percentages, a rollup that groups by two dimensions at once — each is a natural SQL query and an awkward multi-stage pipeline. And because DuckDB reads the log in place and can materialize the parsed data once, the five questions that would be five file reads on the command line become five fast queries against columnar storage. The command line remains the right tool for a quick one-liner — the fastest path to a single number from a raw log — but once you are asking several related analytical questions, the expressiveness and single-parse efficiency of SQL make it both faster to write and faster to run. Recognizing where that crossover sits — roughly, when you want group-by, joins, window functions, or repeated queries — is what tells you to reach for DuckDB rather than stacking more stages onto a pipeline.

Rolling Up by Multiple Dimensions

The real analytical power of SQL over crawl logs shows in multi-dimensional rollups — aggregating by status and section and day at once — which reveal patterns that any single-dimension view hides. A status distribution tells you the overall waste ratio; a per-section breakdown tells you where crawl concentrates; but a rollup that crosses status with section shows you that the waste is concentrated in one section — that, say, your redirects are almost all in the blog archive while your product pages are clean. That crossed view is exactly what tells you where to focus a fix, and it is a single GROUP BY with multiple columns in SQL.

-- Crawl distribution crossed by section and status class — where is the waste?
SELECT '/' || split_part(ltrim(path,'/'),'/',1) AS section,
       sum(CASE WHEN status = 200 THEN 1 ELSE 0 END) AS ok,
       sum(CASE WHEN status >= 300 AND status < 400 THEN 1 ELSE 0 END) AS redirects,
       sum(CASE WHEN status >= 400 THEN 1 ELSE 0 END) AS errors
FROM logs WHERE ua ILIKE '%googlebot%'
GROUP BY section ORDER BY (redirects + errors) DESC LIMIT 15;

Expected Output: one row per section with its productive, redirect, and error counts side by side, ordered by total waste — so a section that is heavily crawled but mostly redirects rises to the top as the highest-leverage fix. This crossed rollup is where SQL earns its place: expressing "waste by section" requires holding two dimensions at once, which conditional aggregation does cleanly in one query. The same technique extends to any pair of dimensions — status by day, section by crawler, hour by status — each revealing a pattern that the marginal, single-dimension views cannot. Multi-dimensional rollups are the analytical capability that turns a pile of log lines into a map of exactly where crawl budget is being spent well and poorly.

An aggregation computed once and discarded answers today's question; the same aggregation exported and accumulated builds a trend, and DuckDB's COPY makes turning a query result into a retained, appendable summary trivial. The pattern is to run the daily rollup, append its output to a small summary file or table, and let that summary accumulate — so over weeks you have a compact history of crawl distribution that you can chart or compare without ever re-reading the raw logs.

This is what elevates the aggregation from a spot check to a monitoring capability. A single day's status distribution tells you today's waste ratio; a summary that accumulates each day's distribution lets you see whether the ratio is improving after a fix, whether a section's crawl is trending down, or whether a seasonal pattern is emerging. Because the summary is tiny — a few rows per day — retaining months of it costs nothing, and it survives long after the raw logs that produced it have been rotated away, giving you long-term crawl history without long-term raw-log storage. Exporting aggregates to a durable summary is therefore both a reporting convenience and the mechanism for building crawl-trend history cheaply, the same aggregate-and-retain economy that lets any log analysis keep the signal while discarding the bulk. It is the natural closing step of a crawl-stats workflow: compute the rollup, export it to the growing summary, and let the trend build itself.

SQL as the Right Abstraction for Crawl Aggregation

The recurring lesson of aggregating crawl stats in DuckDB is that SQL is the right abstraction for the job, and appreciating why keeps you from reaching for clumsier tools when the questions get analytical. Crawl aggregation is fundamentally a set of grouping, ranking, and comparison operations — group by status, rank sections by volume, compare two days, compute shares of a total — and these are exactly what SQL expresses cleanly and executes efficiently. A command-line pipeline can do simple versions, but the moment you want a percentage of total, a multi-dimensional rollup, or a comparison across dates, SQL expresses in one declarative statement what a pipeline needs multiple passes and hand-assembly to achieve.

This fit between the problem and the tool is why moving crawl aggregation into SQL pays off as the analysis deepens. The declarative nature means you describe what you want rather than how to compute it, and the engine optimizes the execution; the expressiveness means complex questions stay concise; and running over a materialized table means the parse cost is paid once. For anything beyond the simplest single-pass count, SQL is both easier to write and faster to run than the equivalent pipeline, which is why DuckDB — full SQL with no server — is such a natural fit for crawl aggregation. Recognizing SQL as the right abstraction for the grouping-and-comparison shape of crawl analysis is what tells you to reach for DuckDB rather than stacking pipeline stages once the questions become genuinely analytical.

Common Mistakes

  • Percentages without a window function. Hard-coding a total from a previous query drifts the moment the data changes. Compute it in-query with OVER ().
  • Grouping on raw paths. Query strings fragment identical pages into thousands of groups. Group on the section or a normalized path, and analyze parameters separately.

Frequently Asked Questions

Can I pivot statuses into columns per day?
Yes — conditional aggregation (sum(CASE WHEN ... )) is the portable pivot, and DuckDB also supports the PIVOT statement for wider reports. For a two-day comparison the CASE form shown above is the clearest.

How do I schedule this as a recurring report?
Wrap the queries in a .sql file and run duckdb crawl.duckdb < report.sql from cron, exporting each result with COPY. Because DuckDB is a single binary with no service, the cron job needs nothing running in the background.

Part of the DuckDB Log Analytics for Crawl Analysis guide.