mirror of
https://github.com/hanzoai/dataroom.git
synced 2026-10-30 18:41:07 +00:00
36 lines
926 B
Plaintext
36 lines
926 B
Plaintext
VERSION 1
|
|
|
|
DESCRIPTION >
|
|
Get total duration and unique countries for all documents in a team
|
|
|
|
NODE get_duration
|
|
SQL >
|
|
%
|
|
SELECT
|
|
sum(duration) as total_duration
|
|
FROM page_views
|
|
WHERE documentId IN splitByString(',', {{String(documentIds, '')}})
|
|
AND time >= {{Int64(since, 0)}}
|
|
AND time < {{Int64(until, 9999999999999)}}
|
|
|
|
NODE get_countries
|
|
SQL >
|
|
%
|
|
SELECT
|
|
groupArray(DISTINCT country) as unique_countries
|
|
FROM pm_click_events
|
|
WHERE document_id IN splitByString(',', {{String(documentIds, '')}})
|
|
AND toUnixTimestamp64Milli(timestamp) >= {{Int64(since, 0)}}
|
|
AND toUnixTimestamp64Milli(timestamp) < {{Int64(until, 9999999999999)}}
|
|
AND country != 'Unknown'
|
|
AND country != ''
|
|
|
|
NODE endpoint
|
|
SQL >
|
|
%
|
|
SELECT
|
|
(SELECT total_duration FROM get_duration) as total_duration,
|
|
(SELECT unique_countries FROM get_countries) as unique_countries
|
|
|
|
|