Papermark is the open-source DocSend alternative and secure data rooms with built-in analytics and custom domains.
VERSION 5
NODE endpoint
SQL >
%
WITH
DistinctDurations AS (
SELECT versionNumber, pageNumber, viewId, SUM(duration) AS distinct_duration
FROM page_views__v3
WHERE
documentId = {{ String(documentId, required=true) }}
AND time >= {{ Int64(since, required=true) }}
AND time <= {{ Int64(until, 9999999999999) }}
AND linkId NOT IN splitByChar(',', {{ String(excludedLinkIds, required=True) }})
AND viewId NOT IN splitByChar(',', {{ String(excludedViewIds, required=True) }})
GROUP BY versionNumber, pageNumber, viewId
)
SELECT versionNumber, pageNumber, AVG(distinct_duration) AS avg_duration
FROM DistinctDurations
GROUP BY versionNumber, pageNumber
ORDER BY versionNumber ASC, pageNumber ASC