Umami is a privacy-first web and product analytics platform. Traffic, campaigns, behavior, conversions, and revenue in one place. No cookies, no surveillance, self-hosted or in the cloud.
TypeScript analyticsaudience-segmentationchartscohort-analysisgoogle-analyticsproduct-analyticsstatisticsuser-journeyweb-analytics
import clickhouse from '@/lib/clickhouse';
import { EVENT_TYPE } from '@/lib/constants';
import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
import prisma from '@/lib/prisma';
import type { QueryFilters } from '@/lib/types';
const FUNCTION_NAME = 'getSessionActivity';
export async function getSessionActivity(
...args: [websiteId: string, sessionIds: string[], filters: QueryFilters]
) {
return runQuery({
[PRISMA]: () => relationalQuery(...args),
[CLICKHOUSE]: () => clickhouseQuery(...args),
});
}
async function relationalQuery(websiteId: string, sessionIds: string[], filters: QueryFilters) {
const { rawQuery } = prisma;
const { startDate, endDate } = filters;
return rawQuery(
`
select
created_at as "createdAt",
url_path as "urlPath",
url_query as "urlQuery",
referrer_domain as "referrerDomain",
event_id as "eventId",
event_type as "eventType",
event_name as "eventName",
visit_id as "visitId",
hostname,
EXISTS (select 1
from event_data d
where d.website_event_id = e.event_id
and d.website_id = {{websiteId::uuid}}
and d.created_at between {{startDate}} and {{endDate}}) AS "hasData"
from website_event e
where website_id = {{websiteId::uuid}}
and session_id = any({{sessionIds}}::uuid[])
and event_type != ${EVENT_TYPE.performance}
and created_at between {{startDate}} and {{endDate}}
order by created_at desc
limit 500
`,
{ websiteId, sessionIds, startDate, endDate },
FUNCTION_NAME,
);
}
async function clickhouseQuery(websiteId: string, sessionIds: string[], filters: QueryFilters) {
const { rawQuery } = clickhouse;
const { startDate, endDate } = filters;
return rawQuery(
`
select
created_at as createdAt,
url_path as urlPath,
url_query as urlQuery,
referrer_domain as referrerDomain,
event_id as eventId,
event_type as eventType,
event_name as eventName,
visit_id as visitId,
hostname,
event_id IN (select event_id
from event_data
where website_id = {websiteId:UUID}
and session_id in {sessionIds:Array(UUID)}
and created_at between {startDate:DateTime64} and {endDate:DateTime64}) AS hasData
from website_event
where website_id = {websiteId:UUID}
and session_id in {sessionIds:Array(UUID)}
and event_type != ${EVENT_TYPE.performance}
and created_at between {startDate:DateTime64} and {endDate:DateTime64}
order by created_at desc
limit 500
`,
{ websiteId, sessionIds, startDate, endDate },
FUNCTION_NAME,
);
}