Skip to content

Papermark is the open-source DocSend alternative and secure data rooms with built-in analytics and custom domains.

858 B
Raw Blame
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