From 5db6afef2702a9ad0e175df8675dee4607d8476f Mon Sep 17 00:00:00 2001
From: Oscar Najera <hi@oscarnajera.com>
Date: Sun, 9 Feb 2025 20:05:48 +0100
Subject: plot site visits

---
 webstats/queries.sql | 11 +++++++++++
 1 file changed, 11 insertions(+)

(limited to 'webstats/queries.sql')

diff --git a/webstats/queries.sql b/webstats/queries.sql
index 2a3f762..5e16da0 100644
--- a/webstats/queries.sql
+++ b/webstats/queries.sql
@@ -19,3 +19,14 @@ DROP TABLE IF EXISTS STATS
 -- Insert a data point
 INSERT INTO STATS (click, page, referrer, ip, user_agent, title)
     VALUES (:click, :page, :referer, :ip, :user_agent, :title)
+
+
+-- name: activity-stats
+-- timeseries of activity
+SELECT
+    (timestamp / 600) * 600 AS time,
+    count(*)
+FROM
+    STATS
+GROUP BY
+    1;
-- 
cgit v1.2.3