Querying Stats From Org-Roam

You can run queries directly on the org-roam database (it’s just a sqlite database) to get interesting stats about your Zettelkasten. The location of the database can be found by evaluating org-roam-db-location.

Notes per month

-- The file name has the date 'yyyy-mm-dd' embedded in it
-- so taking a substring makes it easy to extract the month
select substr(files.file, 25, 7) as month, count(files.file)
from files
group by month
order by month desc;

Links per note

-- The file name has the date 'yyyy-mm-dd' embedded in it
-- so taking a substring makes it easy to extract the month
select substr(files.file, 25, 7) as month, links.type, cast(count(links.source) as real) / cast(count(distinct files.file) as real )
from files
join links on files.file=links.source
group by month, links.type
order by month desc;

Notes per day

select substr(files.file, 25, 7) as month, cast(count(files.file) as real) / cast(count(distinct substr(files.file, 25, 10)) as real)
from files
group by month
order by month desc;

I’ve now written 500 notes and roughly 84,000 words since May 25, 2020 in my Zettelkasten. Here are a few thoughts and observations.

An Emacs library that recreates Roam (software that implements a Zettelkasten-like system) using org-mode.