Querying Stats From Org-Roam

You can run queries directly on the org-roam database directly (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.

Note: file names have the date embedded in it formatted as yyyy-mm-dd so taking a substring is the easiest way to group by dates.

(Updated for org-roam v2)

Total notes (not journal entries)

select count(*) from files where file not like '%journal%';

Notes per year (not journal entries)

select substr(files.file, 24, 4) as year, count(files.file)
from files
where file not like '%journal%'
group by year
order by year desc;

Notes per month

select substr(files.file, 24, 7) as month, count(files.file)
from files
group by month
order by month desc;

Links per note

select substr(files.file, 24, 7) as month, round(cast(count(links.source) as real) / cast(count(distinct files.file) as real), 2)
from files
join nodes on files.file=nodes.file
join links on nodes.id=links.source
where links.type = '"id"'
group by month
order by month desc;

Notes per day

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

Words written for a given year

Counting words written in org-roam