display history of topic updates on topic details page

[websub-hub] / src / db / postgres / sql / topic-publish-history.sql

diff --git a/src/db/postgres/sql/topic-publish-history.sql b/src/db/postgres/sql/topic-publish-history.sql
new file mode 100644 (file)
index 0000000..e4010e8
--- /dev/null
+++ b/src/db/postgres/sql/topic-publish-history.sql
@@ -0,0 +1,12 @@
+--
+SELECT
+       EXTRACT(DAY FROM now() - content_updated::date) AS days_ago,
+       count(*) AS content_updates
+FROM topic_content_history
+WHERE
+       (now() - content_updated::date) < ($(daysAgo) * INTERVAL '1 day')
+AND
+       ($(topicIds) IS NULL OR topic_id = ANY($(topicIds)::uuid[]))
+GROUP BY content_updated::date
+ORDER BY content_updated::date
+;