}
const watchTimeQuery = `SELECT ` +
+ `COUNT("localVideoViewer"."id") AS "totalViewers", ` +
`SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
`AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
`FROM "localVideoViewer" ` +
const watchPeakQuery = `WITH "watchPeakValues" AS (
SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
FROM "localVideoViewer"
- WHERE "videoId" = :videoId
+ WHERE "videoId" = :videoId ${dateWhere}
UNION ALL
SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
FROM "localVideoViewer"
countriesPromise
])
+ const viewersPeak = rowsWatchPeak.length !== 0
+ ? parseInt(rowsWatchPeak[0].concurrent) || 0
+ : 0
+
return {
totalWatchTime: rowsWatchTime.length !== 0
? Math.round(rowsWatchTime[0].totalWatchTime) || 0
? Math.round(rowsWatchTime[0].averageWatchTime) || 0
: 0,
- viewersPeak: rowsWatchPeak.length !== 0
- ? parseInt(rowsWatchPeak[0].concurrent) || 0
+ totalViewers: rowsWatchTime.length !== 0
+ ? Math.round(rowsWatchTime[0].totalViewers) || 0
: 0,
- viewersPeakDate: rowsWatchPeak.length !== 0
+
+ viewersPeak,
+ viewersPeakDate: rowsWatchPeak.length !== 0 && viewersPeak !== 0
? rowsWatchPeak[0].dateBreakpoint || null
: null,
aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
}
+ const intervalWhere: { [ id in VideoStatsTimeserieMetric ]: string } = {
+ // Viewer is still in the interval. Overlap algorithm
+ viewers: '"localVideoViewer"."startDate" <= "intervals"."endDate" ' +
+ 'AND "localVideoViewer"."endDate" >= "intervals"."startDate"',
+
+ // We do an aggregation, so only sum things once. Arbitrary we use the end date for that purpose
+ aggregateWatchTime: '"localVideoViewer"."endDate" >= "intervals"."startDate" ' +
+ 'AND "localVideoViewer"."endDate" <= "intervals"."endDate"'
+ }
+
const query = `WITH "intervals" AS (
SELECT
"time" AS "startDate", "time" + :groupInterval::interval as "endDate"
FROM
intervals
LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
- AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate"
+ AND ${intervalWhere[metric]}
GROUP BY
"intervals"."startDate"
ORDER BY