dataroom/lib/tinybird/endpoints/get_total_average_page_duration.pipe

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 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