Open source Loom alternative. Beautiful, shareable screen recordings.
Rust appcapcossloommacnextjsnextjs15open-sourceossreactrecordrustscreen-capturescreen-recorderscreenshotsolidjstauritauri-apptypescriptvite
import { db } from "@cap/database";
import { comments, spaceVideos, videos } from "@cap/database/schema";
import { Tinybird } from "@cap/web-backend";
import { and, between, eq, inArray } from "drizzle-orm";
import { type SQL, sql } from "drizzle-orm/sql";
import { Effect } from "effect";
import { videoBelongsToOrganization } from "@/lib/cap-analytics-access";
import { runPromise } from "@/lib/server";
import type {
AnalyticsRange,
BreakdownRow,
OrgAnalyticsResponse,
} from "./types";
type VideoRow = typeof videos.$inferSelect;
type OrgId = VideoRow["orgId"];
type VideoId = VideoRow["id"];
type SpaceVideoRow = typeof spaceVideos.$inferSelect;
type SpaceOrOrgId = SpaceVideoRow["spaceId"];
type CountSeriesRow = { bucket: string; count: number };
type ViewSeriesRow = { bucket: string; views: number };
type BreakdownSourceRow = { name: string; views: number; subtitle?: string };
type TopCapRow = { videoId: string; views: number };
type TinybirdAnalyticsData = {
viewSeries: ViewSeriesRow[];
countries: BreakdownSourceRow[];
cities: BreakdownSourceRow[];
browsers: BreakdownSourceRow[];
devices: BreakdownSourceRow[];
operatingSystems: BreakdownSourceRow[];
topCapsRaw: TopCapRow[];
};
type RollingAnalyticsRange = Exclude<AnalyticsRange, "lifetime">;
const ROLLING_RANGE_CONFIG: Record<
RollingAnalyticsRange,
{ hours: number; bucket: "hour" | "day" }
> = {
"24h": { hours: 24, bucket: "hour" },
"7d": { hours: 7 * 24, bucket: "day" },
"30d": { hours: 30 * 24, bucket: "day" },
};
const LIFETIME_FALLBACK_DAYS = 30;
// Escape backslashes BEFORE quotes: ClickHouse honors C-style `\'` inside
// single-quoted literals, so doubling quotes alone lets a trailing backslash
// neutralise the doubled quote and break out of the string (SQL injection).
const escapeLiteral = (value: string) =>
value.replace(/\\/g, "\\\\").replace(/'/g, "''");
const toDateString = (date: Date) => date.toISOString().slice(0, 10);
const toDateTimeString = (date: Date) =>
date.toISOString().slice(0, 19).replace("T", " ");
const buildPathnameFilter = (spaceVideoIds?: VideoId[]): string => {
if (!spaceVideoIds || spaceVideoIds.length === 0) {
return "";
}
const pathnames = spaceVideoIds
.map((id) => `'/s/${escapeLiteral(id)}'`)
.join(", ");
return `AND pathname IN (${pathnames})`;
};
const normalizeBucket = (
input: string | null | undefined,
bucket: "hour" | "day",
) => {
if (!input) return undefined;
if (input.endsWith("Z")) return input;
if (bucket === "day" && input.length === 10) return `${input}T00:00:00Z`;
return `${input.replace(" ", "T")}Z`;
};
const formatBucketTimestamp = (value: Date) =>
// Strip milliseconds so we can string-compare with MySQL/Tinybird buckets
`${value.toISOString().slice(0, 19)}Z`;
const buildBuckets = (from: Date, to: Date, bucket: "hour" | "day") => {
const size = bucket === "hour" ? 60 * 60 * 1000 : 24 * 60 * 60 * 1000;
const buckets: string[] = [];
const start = Math.floor(from.getTime() / size) * size;
const end = Math.floor(to.getTime() / size) * size;
for (let ts = start; ts <= end; ts += size) {
buckets.push(formatBucketTimestamp(new Date(ts)));
}
return buckets;
};
const getLifetimeRangeStart = async (
orgId: OrgId,
videoIds?: VideoId[],
): Promise<Date | undefined> => {
const whereClause =
videoIds && videoIds.length > 0
? and(eq(videos.orgId, orgId), inArray(videos.id, videoIds))
: eq(videos.orgId, orgId);
const rows = await db()
.select({ minCreatedAt: sql<Date>`MIN(${videos.createdAt})` })
.from(videos)
.where(whereClause)
.limit(1);
const candidate = rows[0]?.minCreatedAt;
if (!candidate) return undefined;
return candidate instanceof Date ? candidate : new Date(candidate);
};
const resolveRangeBounds = async (
range: AnalyticsRange,
orgId: OrgId,
videoIds?: VideoId[],
): Promise<{ from: Date; to: Date; bucket: "hour" | "day" }> => {
const to = new Date();
if (range === "lifetime") {
const lifetimeStart = await getLifetimeRangeStart(orgId, videoIds);
const fallbackFrom = new Date(
to.getTime() - LIFETIME_FALLBACK_DAYS * 24 * 60 * 60 * 1000,
);
const from =
lifetimeStart && lifetimeStart < to ? lifetimeStart : fallbackFrom;
return { from, to, bucket: "day" };
}
const config = ROLLING_RANGE_CONFIG[range];
const from = new Date(to.getTime() - config.hours * 60 * 60 * 1000);
return { from, to, bucket: config.bucket };
};
const fallbackIfEmpty = <Row>(
primary: Effect.Effect<Row[], never, never>,
fallback?: Effect.Effect<Row[], never, never>,
) =>
fallback
? primary.pipe(
Effect.flatMap((rows: Row[]) =>
rows.length > 0 ? Effect.succeed(rows) : fallback,
),
)
: primary;
const withTinybirdFallback = <Row>(
effect: Effect.Effect<unknown, unknown, never>,
) =>
effect.pipe(
Effect.catchAll((e: unknown) => {
console.error("tinybird query error", e);
return Effect.succeed<{ data: Row[] }>({ data: [] as Row[] });
}),
Effect.map((res: unknown) => {
const response = res as { data?: unknown[] };
const data = response.data ?? [];
return data.filter(
(item): item is Row => typeof item === "object" && item !== null,
) as Row[];
}),
);
const getSpaceVideoIds = async (spaceId: SpaceOrOrgId): Promise<VideoId[]> => {
const rows = await db()
.select({ videoId: spaceVideos.videoId })
.from(spaceVideos)
.where(eq(spaceVideos.spaceId, spaceId));
return rows.map((row) => row.videoId);
};
export const getOrgAnalyticsData = async (
orgId: string,
range: AnalyticsRange,
spaceId?: string,
capId?: string,
): Promise<OrgAnalyticsResponse> => {
const typedOrgId = orgId as OrgId;
const spaceVideoIds = spaceId
? await getSpaceVideoIds(spaceId as SpaceOrOrgId)
: undefined;
const capVideoIds = capId ? [capId as VideoId] : undefined;
const videoIds = capVideoIds || spaceVideoIds;
const { from, to, bucket } = await resolveRangeBounds(
range,
typedOrgId,
videoIds,
);
const buckets = buildBuckets(from, to, bucket);
if (
(spaceId && spaceVideoIds && spaceVideoIds.length === 0) ||
(capId && !capVideoIds)
) {
let capName: string | undefined;
if (capId) {
const capNames = await loadVideoNames([capId]);
capName = capNames.get(capId);
}
return {
counts: {
caps: 0,
views: 0,
comments: 0,
reactions: 0,
},
chart: buckets.map((bucket) => ({
bucket,
caps: 0,
views: 0,
comments: 0,
reactions: 0,
})),
breakdowns: {
countries: [],
cities: [],
browsers: [],
operatingSystems: [],
devices: [],
topCaps: [],
},
capName,
};
}
const [capsSeries, commentSeries, reactionSeries] = await Promise.all([
queryVideoSeries(typedOrgId, from, to, bucket, videoIds),
queryCommentsSeries(typedOrgId, from, to, "text", bucket, videoIds),
queryCommentsSeries(typedOrgId, from, to, "emoji", bucket, videoIds),
]);
const tinybirdData = await runPromise(
Effect.gen(function* () {
const tinybird = yield* Tinybird;
const viewSeries = yield* queryViewSeries(
tinybird,
typedOrgId,
from,
to,
bucket,
videoIds,
);
const countries = yield* queryCountries(
tinybird,
typedOrgId,
from,
to,
videoIds,
);
const cities = yield* queryCities(
tinybird,
typedOrgId,
from,
to,
videoIds,
);
const browsers = yield* queryBrowsers(
tinybird,
typedOrgId,
from,
to,
videoIds,
);
const devices = yield* queryDevices(
tinybird,
typedOrgId,
from,
to,
videoIds,
);
const operatingSystems = yield* queryOperatingSystems(
tinybird,
typedOrgId,
from,
to,
videoIds,
);
const topCapsRaw = capId
? []
: yield* queryTopCaps(tinybird, typedOrgId, from, to, videoIds);
return {
viewSeries,
countries,
cities,
browsers,
devices,
operatingSystems,
topCapsRaw,
} satisfies TinybirdAnalyticsData;
}),
);
const totalViews = tinybirdData.viewSeries.reduce(
(sum: number, row: ViewSeriesRow) => sum + row.views,
0,
);
const totalCaps = capsSeries.reduce(
(sum: number, row: CountSeriesRow) => sum + row.count,
0,
);
const totalComments = commentSeries.reduce(
(sum: number, row: CountSeriesRow) => sum + row.count,
0,
);
const totalReactions = reactionSeries.reduce(
(sum: number, row: CountSeriesRow) => sum + row.count,
0,
);
const chartData = buckets.map((bucket) => ({
bucket,
caps:
capsSeries.find((row: CountSeriesRow) => row.bucket === bucket)?.count ??
0,
views:
tinybirdData.viewSeries.find(
(row: ViewSeriesRow) => row.bucket === bucket,
)?.views ?? 0,
comments:
commentSeries.find((row: CountSeriesRow) => row.bucket === bucket)
?.count ?? 0,
reactions:
reactionSeries.find((row: CountSeriesRow) => row.bucket === bucket)
?.count ?? 0,
}));
const videoNames = await loadVideoNames(
tinybirdData.topCapsRaw
.map((cap: TopCapRow) => cap.videoId)
.filter(Boolean),
videoBelongsToOrganization(typedOrgId),
);
let capName: string | undefined;
if (capId) {
const capNames = await loadVideoNames([capId]);
capName = capNames.get(capId);
}
return {
counts: {
caps: totalCaps,
views: totalViews,
comments: totalComments,
reactions: totalReactions,
},
chart: chartData,
breakdowns: {
countries: formatBreakdown(
tinybirdData.countries,
totalViews,
(row) => row.name,
),
cities: formatBreakdown(
tinybirdData.cities,
totalViews,
(row) => row.name,
),
browsers: normalizeAndAggregate(
tinybirdData.browsers,
totalViews,
(row) => row.name,
normalizeBrowserName,
),
operatingSystems: normalizeAndAggregate(
tinybirdData.operatingSystems,
totalViews,
(row) => row.name,
normalizeOSName,
),
devices: normalizeAndAggregate(
tinybirdData.devices,
totalViews,
(row) => row.name,
normalizeDeviceName,
),
topCaps: tinybirdData.topCapsRaw.map((row: TopCapRow) => ({
id: row.videoId,
name: videoNames.get(row.videoId) ?? row.videoId,
views: row.views,
percentage: totalViews > 0 ? row.views / totalViews : 0,
})),
},
capName,
};
};
const normalizeOSName = (name: string): string => {
if (!name || typeof name !== "string") return name || "Unknown";
const normalized = name.trim();
if (!normalized) return "Unknown";
const lower = normalized.toLowerCase();
if (
lower.includes("mac") ||
lower === "macos" ||
lower === "mac os" ||
lower === "darwin"
) {
return "macOS";
}
if (lower.includes("windows") || lower === "win") {
return "Windows";
}
if (lower.includes("linux")) {
return "Linux";
}
if (lower.includes("android")) {
return "Android";
}
if (lower.includes("ios") || lower === "iphone os") {
return "iOS";
}
if (lower.includes("ubuntu")) {
return "Ubuntu";
}
if (lower.includes("fedora")) {
return "Fedora";
}
return normalized;
};
const normalizeDeviceName = (name: string): string => {
if (!name || typeof name !== "string") return name || "Unknown";
const normalized = name.trim();
if (!normalized) return "Unknown";
const lower = normalized.toLowerCase();
if (lower === "desktop" || lower === "desktop computer" || lower === "pc") {
return "Desktop";
}
if (lower === "mobile" || lower === "smartphone" || lower === "phone") {
return "Mobile";
}
if (lower === "tablet" || lower === "ipad") {
return "Tablet";
}
return normalized;
};
const normalizeBrowserName = (name: string): string => {
if (!name || typeof name !== "string") return name || "Unknown";
const normalized = name.trim();
if (!normalized) return "Unknown";
const lower = normalized.toLowerCase();
if (lower.includes("chrome") && !lower.includes("chromium")) {
return "Chrome";
}
if (lower.includes("firefox")) {
return "Firefox";
}
if (lower.includes("safari")) {
return "Safari";
}
if (lower.includes("edge")) {
return "Edge";
}
if (lower.includes("opera")) {
return "Opera";
}
if (lower.includes("brave")) {
return "Brave";
}
if (lower.includes("internet explorer") || lower === "ie") {
return "Internet Explorer";
}
return normalized;
};
const normalizeAndAggregate = <T extends { name: string; views: number }>(
rows: T[],
totalViews: number,
getName: (row: T) => string,
normalizeFn: (name: string) => string,
): BreakdownRow[] => {
const aggregated = new Map<string, number>();
for (const row of rows) {
const originalName = getName(row);
const normalizedName = normalizeFn(originalName);
const currentViews = aggregated.get(normalizedName) ?? 0;
aggregated.set(normalizedName, currentViews + row.views);
}
return Array.from(aggregated.entries())
.map(([name, views]) => ({
name,
views,
percentage: totalViews > 0 ? views / totalViews : 0,
}))
.sort((a, b) => b.views - a.views);
};
const formatBreakdown = <T extends { name: string; views: number }>(
rows: T[],
totalViews: number,
getName: (row: T) => string,
): BreakdownRow[] =>
rows.map((row) => ({
name: getName(row),
views: row.views,
percentage: totalViews > 0 ? row.views / totalViews : 0,
}));
const loadVideoNames = async (videoIds: ReadonlyArray<string>, scope?: SQL) => {
if (videoIds.length === 0) return new Map<string, string>();
const typedVideoIds = videoIds.map((id) => id as VideoId);
const records = await db()
.select({ id: videos.id, name: videos.name })
.from(videos)
.where(and(inArray(videos.id, typedVideoIds), scope));
return new Map(records.map((record) => [record.id, record.name]));
};
const queryVideoSeries = async (
orgId: OrgId,
from: Date,
to: Date,
bucket: "hour" | "day",
spaceVideoIds?: VideoId[],
): Promise<CountSeriesRow[]> => {
const bucketExpr =
bucket === "hour"
? sql<string>`DATE_FORMAT(${videos.createdAt}, '%Y-%m-%dT%H:00:00Z')`
: sql<string>`DATE_FORMAT(${videos.createdAt}, '%Y-%m-%dT00:00:00Z')`;
const conditions = [
eq(videos.orgId, orgId),
between(videos.createdAt, from, to),
];
if (spaceVideoIds && spaceVideoIds.length > 0) {
conditions.push(inArray(videos.id, spaceVideoIds));
}
const rows = await db()
.select({ bucket: bucketExpr, count: sql<number>`COUNT(*)` })
.from(videos)
.where(and(...conditions))
.groupBy(bucketExpr);
return rows
.map((row) => ({
bucket: normalizeBucket(row.bucket, bucket),
count: Number(row.count) || 0,
}))
.filter((row): row is CountSeriesRow => Boolean(row.bucket));
};
const queryCommentsSeries = async (
orgId: OrgId,
from: Date,
to: Date,
type: "text" | "emoji",
bucket: "hour" | "day",
spaceVideoIds?: VideoId[],
): Promise<CountSeriesRow[]> => {
const column = comments.createdAt;
const bucketExpr =
bucket === "hour"
? sql<string>`DATE_FORMAT(${column}, '%Y-%m-%dT%H:00:00Z')`
: sql<string>`DATE_FORMAT(${column}, '%Y-%m-%dT00:00:00Z')`;
const conditions = [
eq(videos.orgId, orgId),
eq(comments.type, type),
between(comments.createdAt, from, to),
];
if (spaceVideoIds && spaceVideoIds.length > 0) {
conditions.push(inArray(videos.id, spaceVideoIds));
}
const rows = await db()
.select({ bucket: bucketExpr, count: sql<number>`COUNT(*)` })
.from(comments)
.innerJoin(videos, eq(comments.videoId, videos.id))
.where(and(...conditions))
.groupBy(bucketExpr);
return rows
.map((row) => ({
bucket: normalizeBucket(row.bucket, bucket),
count: Number(row.count) || 0,
}))
.filter((row): row is CountSeriesRow => Boolean(row.bucket));
};
type TinybirdService = Effect.Effect.Success<typeof Tinybird>;
const queryViewSeries = (
tinybird: TinybirdService,
orgId: OrgId,
from: Date,
to: Date,
bucket: "hour" | "day",
spaceVideoIds?: VideoId[],
) => {
const pathnameFilter = buildPathnameFilter(spaceVideoIds);
const bucketFormatter =
bucket === "hour" ? "%Y-%m-%dT%H:00:00Z" : "%Y-%m-%dT00:00:00Z";
const rawSql = `
SELECT
formatDateTime(${bucket === "hour" ? "toStartOfHour" : "toStartOfDay"}(timestamp), '${bucketFormatter}') as bucket,
uniq(session_id) as views
FROM analytics_events
WHERE action = 'page_hit'
AND tenant_id = '${escapeLiteral(orgId)}'
AND timestamp BETWEEN toDateTime('${toDateTimeString(from)}') AND toDateTime('${toDateTimeString(to)}')
${pathnameFilter}
GROUP BY bucket
ORDER BY bucket
`;
const aggregatedSql = `
SELECT
formatDateTime(date, '${bucketFormatter}') as bucket,
uniqMerge(visits) as views
FROM analytics_pages_mv
WHERE tenant_id = '${escapeLiteral(orgId)}'
AND date BETWEEN toDate('${toDateString(from)}') AND toDate('${toDateString(to)}')
${pathnameFilter}
GROUP BY bucket
ORDER BY bucket
`;
type Row = { bucket: string; views: number };
const rawEffect = withTinybirdFallback<Row>(tinybird.querySql<Row>(rawSql));
const effect =
bucket === "hour"
? rawEffect
: fallbackIfEmpty(
withTinybirdFallback<Row>(tinybird.querySql<Row>(aggregatedSql)),
rawEffect,
);
return effect.pipe(
Effect.map((rows: Row[]) =>
rows.map((row: Row) => ({
bucket: row.bucket,
views: Number(row.views) || 0,
})),
),
);
};
const queryCountries = (
tinybird: TinybirdService,
orgId: OrgId,
from: Date,
to: Date,
spaceVideoIds?: VideoId[],
) => {
const pathnameFilter = buildPathnameFilter(spaceVideoIds);
const aggregatedSql = `
SELECT
country as name,
uniqMerge(visits) as views
FROM analytics_pages_mv
WHERE tenant_id = '${escapeLiteral(orgId)}'
AND date BETWEEN toDate('${toDateString(from)}') AND toDate('${toDateString(to)}')
AND country != ''
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
const rawSql = `
SELECT
coalesce(nullIf(country, ''), '') as name,
uniq(session_id) as views
FROM analytics_events
WHERE action = 'page_hit'
AND tenant_id = '${escapeLiteral(orgId)}'
AND timestamp BETWEEN toDateTime('${toDateTimeString(from)}') AND toDateTime('${toDateTimeString(to)}')
AND country != ''
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
type Row = { name: string; views: number };
return fallbackIfEmpty(
withTinybirdFallback<Row>(tinybird.querySql<Row>(aggregatedSql)),
withTinybirdFallback<Row>(tinybird.querySql<Row>(rawSql)),
).pipe(
Effect.map((rows: Row[]) =>
rows.map((row: Row) => ({
name: row.name,
views: Number(row.views) || 0,
})),
),
);
};
const queryCities = (
tinybird: TinybirdService,
orgId: OrgId,
from: Date,
to: Date,
spaceVideoIds?: VideoId[],
) => {
const pathnameFilter = buildPathnameFilter(spaceVideoIds);
const aggregatedSql = `
SELECT
country as country,
city as city,
uniqMerge(visits) as views
FROM analytics_pages_mv
WHERE tenant_id = '${escapeLiteral(orgId)}'
AND date BETWEEN toDate('${toDateString(from)}') AND toDate('${toDateString(to)}')
AND city != ''
${pathnameFilter}
GROUP BY country, city
ORDER BY views DESC
LIMIT 10
`;
const rawSql = `
SELECT
coalesce(nullIf(country, ''), '') as country,
coalesce(nullIf(city, ''), '') as city,
uniq(session_id) as views
FROM analytics_events
WHERE action = 'page_hit'
AND tenant_id = '${escapeLiteral(orgId)}'
AND timestamp BETWEEN toDateTime('${toDateTimeString(from)}') AND toDateTime('${toDateTimeString(to)}')
AND city != ''
${pathnameFilter}
GROUP BY country, city
ORDER BY views DESC
LIMIT 10
`;
type Row = { country: string; city: string; views: number };
return fallbackIfEmpty(
withTinybirdFallback<Row>(tinybird.querySql<Row>(aggregatedSql)),
withTinybirdFallback<Row>(tinybird.querySql<Row>(rawSql)),
).pipe(
Effect.map((rows: Row[]) =>
rows.map((row: Row) => ({
name: row.city,
subtitle: row.country,
views: Number(row.views) || 0,
})),
),
);
};
const queryBrowsers = (
tinybird: TinybirdService,
orgId: OrgId,
from: Date,
to: Date,
spaceVideoIds?: VideoId[],
) => {
const pathnameFilter = buildPathnameFilter(spaceVideoIds);
const aggregatedSql = `
SELECT
browser as name,
uniq(session_id) as views
FROM analytics_sessions_mv
WHERE tenant_id = '${escapeLiteral(orgId)}'
AND date BETWEEN toDate('${toDateString(from)}') AND toDate('${toDateString(to)}')
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
const rawSql = `
SELECT
coalesce(nullIf(browser, ''), 'unknown') as name,
uniq(session_id) as views
FROM analytics_events
WHERE action = 'page_hit'
AND tenant_id = '${escapeLiteral(orgId)}'
AND timestamp BETWEEN toDateTime('${toDateTimeString(from)}') AND toDateTime('${toDateTimeString(to)}')
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
type Row = { name: string; views: number };
const rawEffect = withTinybirdFallback<Row>(tinybird.querySql<Row>(rawSql));
const effect = pathnameFilter
? rawEffect
: fallbackIfEmpty(
withTinybirdFallback<Row>(tinybird.querySql<Row>(aggregatedSql)),
rawEffect,
);
return effect.pipe(
Effect.map((rows: Row[]) =>
rows.map((row: Row) => ({
name: row.name,
views: Number(row.views) || 0,
})),
),
);
};
const queryDevices = (
tinybird: TinybirdService,
orgId: OrgId,
from: Date,
to: Date,
spaceVideoIds?: VideoId[],
) => {
const pathnameFilter = buildPathnameFilter(spaceVideoIds);
const aggregatedSql = `
SELECT
device as name,
uniq(session_id) as views
FROM analytics_sessions_mv
WHERE tenant_id = '${escapeLiteral(orgId)}'
AND date BETWEEN toDate('${toDateString(from)}') AND toDate('${toDateString(to)}')
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
const rawSql = `
SELECT
coalesce(nullIf(device, ''), 'desktop') as name,
uniq(session_id) as views
FROM analytics_events
WHERE action = 'page_hit'
AND tenant_id = '${escapeLiteral(orgId)}'
AND timestamp BETWEEN toDateTime('${toDateTimeString(from)}') AND toDateTime('${toDateTimeString(to)}')
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
type Row = { name: string; views: number };
const rawEffect = withTinybirdFallback<Row>(tinybird.querySql<Row>(rawSql));
const effect = pathnameFilter
? rawEffect
: fallbackIfEmpty(
withTinybirdFallback<Row>(tinybird.querySql<Row>(aggregatedSql)),
rawEffect,
);
return effect.pipe(
Effect.map((rows: Row[]) =>
rows.map((row: Row) => ({
name: row.name,
views: Number(row.views) || 0,
})),
),
);
};
const queryOperatingSystems = (
tinybird: TinybirdService,
orgId: OrgId,
from: Date,
to: Date,
spaceVideoIds?: VideoId[],
) => {
const pathnameFilter = buildPathnameFilter(spaceVideoIds);
const aggregatedSql = `
SELECT
os as name,
uniq(session_id) as views
FROM analytics_sessions_mv
WHERE tenant_id = '${escapeLiteral(orgId)}'
AND date BETWEEN toDate('${toDateString(from)}') AND toDate('${toDateString(to)}')
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
const rawSql = `
SELECT
coalesce(nullIf(os, ''), 'unknown') as name,
uniq(session_id) as views
FROM analytics_events
WHERE action = 'page_hit'
AND tenant_id = '${escapeLiteral(orgId)}'
AND timestamp BETWEEN toDateTime('${toDateTimeString(from)}') AND toDateTime('${toDateTimeString(to)}')
${pathnameFilter}
GROUP BY name
ORDER BY views DESC
LIMIT 10
`;
type Row = { name: string; views: number };
const rawEffect = withTinybirdFallback<Row>(tinybird.querySql<Row>(rawSql));
const effect = pathnameFilter
? rawEffect
: fallbackIfEmpty(
withTinybirdFallback<Row>(tinybird.querySql<Row>(aggregatedSql)),
rawEffect,
);
return effect.pipe(
Effect.map((rows: Row[]) =>
rows.map((row: Row) => ({
name: row.name,
views: Number(row.views) || 0,
})),
),
);
};
const queryTopCaps = (
tinybird: TinybirdService,
orgId: OrgId,
from: Date,
to: Date,
spaceVideoIds?: VideoId[],
) => {
const pathnameFilter = buildPathnameFilter(spaceVideoIds);
const aggregatedSql = `
SELECT
pathname,
uniqMerge(visits) as views
FROM analytics_pages_mv
WHERE tenant_id = '${escapeLiteral(orgId)}'
AND date BETWEEN toDate('${toDateString(from)}') AND toDate('${toDateString(to)}')
AND startsWith(pathname, '/s/')
${pathnameFilter}
GROUP BY pathname
ORDER BY views DESC
LIMIT 10
`;
const rawSql = `
SELECT
coalesce(pathname, '') as pathname,
uniq(session_id) as views
FROM analytics_events
WHERE action = 'page_hit'
AND tenant_id = '${escapeLiteral(orgId)}'
AND timestamp BETWEEN toDateTime('${toDateTimeString(from)}') AND toDateTime('${toDateTimeString(to)}')
${pathnameFilter}
GROUP BY pathname
HAVING startsWith(pathname, '/s/')
ORDER BY views DESC
LIMIT 10
`;
type Row = { pathname: string; views: number };
return fallbackIfEmpty(
withTinybirdFallback<Row>(tinybird.querySql<Row>(aggregatedSql)),
withTinybirdFallback<Row>(tinybird.querySql<Row>(rawSql)),
).pipe(
Effect.map((rows: Row[]) =>
rows
.map((row: Row) => ({
videoId: row.pathname?.split("/s/")[1] ?? row.pathname,
views: Number(row.views) || 0,
}))
.filter((row: { videoId: string; views: number }): row is TopCapRow =>
Boolean(row.videoId),
),
),
);
};