Absolutely! A starting point would be something like this to calculate total busy time per day:
SELECT sum(end - start) from events group by JULIANDAY(start)
Some extra aggregation and you could totally calculate your busy/free ratio
This is one of the queries I'm using to track a rolling average of how many times a week I've been cycling:
WITH recursive dates(day) AS
(
SELECT date($__unixepochfrom(), 'unixepoch')
UNION ALL
SELECT date(day, '+1 DAY')
FROM dates
WHERE day<date($__unixepochto(), 'unixepoch') )
SELECT day AS time,
(
SELECT count(DISTINCT julianday(start))
FROM events
WHERE summary = " Cycling"
AND start <= date(day, '+1 DAY')
AND start > date(day, '-7 DAY')) AS cycling,
FROM dates
ORDER BY day DESC
And yeah 100% admit the documentation is lacking. I'll be honest, I got to the 3 week mark of building and decided I need to validate the idea before putting any more work in.
Going to be adding some more pre-done queries like this as inspiration.
This is one of the queries I'm using to track a rolling average of how many times a week I've been cycling: WITH recursive dates(day) AS ( SELECT date($__unixepochfrom(), 'unixepoch') UNION ALL SELECT date(day, '+1 DAY') FROM dates WHERE day<date($__unixepochto(), 'unixepoch') ) SELECT day AS time, ( SELECT count(DISTINCT julianday(start)) FROM events WHERE summary = " Cycling" AND start <= date(day, '+1 DAY') AND start > date(day, '-7 DAY')) AS cycling, FROM dates ORDER BY day DESC
And yeah 100% admit the documentation is lacking. I'll be honest, I got to the 3 week mark of building and decided I need to validate the idea before putting any more work in. Going to be adding some more pre-done queries like this as inspiration.