Analyze Git Logs in Style With DuckDB

Every developer knows about the git log command. But did you know that you can figure out funky stuff if you combine it with DuckDB?

programming

Sometimes I find myself in a situation where I want to learn more about the history of a git repository. The git history can tell me a lot about the obvious stuff (“what happened recently in this repo?”, “when was this particular change made and by whom?”) but it can hold much more information than you’d think. I’ve looked at the git history to figure out things like:

  1. Which engineer might know the most about a certain topic?
  2. Are contributions to this repository slowing down or speeding up?
  3. Are we doing enough refactoring and maintenance work? Who’s doing it?
  4. Are people on my team burning the midnight oil and should I be worried about this?

The Basics

Git’s built-in git log packs quite a punch and offers a lot of filters you can use to query your git history. Besides just seeing what happened recently (via a simple git log) you can go much deeper.

A few examples:

git shortlog -sn shows you the number of commits per author, which can be handy to figure out who’s contributing to a repository the most.

git log --since="4 weeks ago" gives you all commits of the last 4 weeks.

git log --author="Troy McClure" gives you all commits by author “Troy McClure”. Combine those filters as you like.

git log -S "console.log" --oneline lists all the commits that added or removed a console.log statement to/from the current codebase (git log -S is also called the pickaxe command, if you want to dig deeper). This could also be a fun way to figure out who of your developers swears the most in your code base (it’s Claude, isn’t it?!).

You can combine all these various filters, queries, and formatting options to narrow down what you want. And while git log is ubiquitous and quite powerful, we want to go further. And we want to use the power of SQL while we’re at it.

SQL Queries for Your Git History

Let’s add DuckDB to the mix. This handy swiss army knife of a database is worth keeping around because it makes exploring random datasets just so fun and easy. Analyzing our git logs is quite simple:

  1. We use git log to format our git history as a CSV file
  2. We load that CSV file into DuckDB
  3. And we’re ready to query away

1. Format Your Git History as CSV

First, we assemble the git log as a CSV file. We can use git log’s --pretty=format:'...' syntax combined with a small echo statement to provide the CSV header line:

echo "author;commit;subject;notes;date\n$(git log --pretty=format:'%aN;%H;%s;%N;%aI')" > log.csv

You can add more fields if you like but I found those to be the most useful ones. See the git log pretty format docs for available fields. Make sure to add new fields both for the CSV headers (the first part of the echo statement) and the fields provided via the format string for the git log command.

2. Load Your History Into DuckDB

Install DuckDB and start it on your command line using the duckdb command.

Load your log.csv file into a table:

CREATE TABLE commits AS
  SELECT *
  FROM read_json('log.csv');

Verify that it worked using

DESCRIBE commits;
Expected Result 
┌──────────────────────────────────┐
│             commits              │
│                                  │
│ author  varchar                  │
│ commit  varchar                  │
│ subject varchar                  │
│ notes   varchar                  │
│ date    timestamp with time zone │
└──────────────────────────────────┘

3. Query Away

With your git history stored in DuckDB, you can use plain old SQL and powerful filter, aggregation, and window functions to slice and dice your git history.

Here are a few interesting examples. I’m sure there are more queries you can come up with. You can also ask your AI of choice for help if SQL is not your strong suit.

Who’s the Housekeeper?

When using conventional commits you can get a sense for who’s doing the most cleanup tasks:

SELECT author, count(1) as 'commit_count'
FROM commits
WHERE subject ILIKE 'chore%'
GROUP BY author;

Also worth trying: LIKE 'perf%' for performance tweaks or LIKE 'design%' for pixel pushing.

How Many Bugs Do We Fix?

Similar to filtering for chore% commits, you could try to find the number of bugfixes in a given time period (again, conventional commits could come in handy here but are not a must):

SELECT count(1) as 'bugfix_count'
FROM commits
WHERE subject ILIKE 'fix%';

Is the Number of Refactorings Going Up or Down Over Time?

WITH commit_stats AS (
  SELECT
    year(date) as 'year',
    month(date) as 'month'
  FROM commits
  WHERE subject ILIKE '%refactor%'
)
SELECT
  year,
  month,
  count(*) as 'commit_count'
FROM commit_stats
GROUP BY year, month
ORDER BY year, month;
Result 
┌───────┬───────┬──────────────┐
│ year  │ month │ commit count │
│ int64 │ int64 │    int64     │
├───────┼───────┼──────────────┤
│  2024 │     6 │            1 │
│  2024 │    11 │            1 │
│  2024 │    12 │            3 │
│  2025 │     1 │            2 │
│  2025 │     2 │            3 │
│  2025 │     4 │            9 │
│  2025 │     7 │            1 │
│  2025 │     8 │            1 │
│  2025 │    10 │            2 │
│  2025 │    12 │            3 │
│  2026 │     1 │            3 │
│  2026 │     3 │            2 │
│  2026 │     4 │            1 │
│  2026 │     5 │            5 │
└───────┴───────┴──────────────┘

What’s the Ratio of Feature to Maintenance Commits?

SELECT
  CASE
    WHEN subject ILIKE '%fix%'   THEN 'Fix'
    WHEN subject ILIKE '%chore%' THEN 'Chore'
    ELSE 'Feature'
  END AS commit_category,
  count(*) AS commit_count,
  round(100.0 * commit_count / sum(commit_count) OVER (), 2) AS percentage
FROM commits
GROUP BY 1
ORDER BY commit_count DESC;
Result 
┌─────────────────┬──────────────┬────────────┐
│ commit_category │ commit_count │ percentage │
│     varchar     │    int64     │   double   │
├─────────────────┼──────────────┼────────────┤
│ Feature / Other │          665 │       90.6 │
│ Fix             │           60 │       8.17 │
│ Chore           │            9 │       1.23 │
└─────────────────┴──────────────┴────────────┘

Multiple Git Repositories

If you want to combine the history of multiple git repositories (maybe you’re working in a microservices environment or a similar poly-repo setup) you’ve got two options.

Either load multiple history CSV files into the same table:

CREATE TABLE commits AS
  SELECT *
  FROM read_json(['repo-1.csv', 'repo-2.csv']);

…or load them into separate tables initially and UNION them at query time:

WITH all_commits AS (
  SELECT 'repo_one' AS repo, * FROM commits_one
  UNION ALL
  SELECT 'repo_two' AS repo, * FROM commits_two
)
SELECT * FROM all_commits;

Why Is This Useful?

In the past I’ve used this approach as a simple way to get an overview of the dynamics on new teams I’ve joined or on teams I’ve managed. I used it to better understand pockets of expertise, contribution patterns, or how code evolves over time.

I did these things in back when (*gasp*) humans wrote code by hand. I don’t know how useful these things are in a world where clankers take all that fun away from us. Maybe it’s even more helpful because you want to ensure that refactoring still happens, that bugfixes don’t skyrocket, maybe it’s not. I’ll leave this up to you to figure out.

One parting note: If you manage a team of engineers and use any of this to measure the performance and productivity of individual engineers, please just stop. The work of your engineers is more complex than the commits they leave behind can tell you.

If you found other cool uses and queries that you’d like to share send me a note (contact details are in the footer).