MCPcopy Create free account
hub / github.com/Openpanel-dev/openpanel / buildSql

Function buildSql

packages/db/scripts/backfill-profile-seen.ts:58–104  ·  view source on GitHub ↗
()

Source from the content-addressed store, hash-verified

56);
57
58function buildSql(): string {
59 // `join_use_nulls = 1` makes unmatched LEFT JOIN columns NULL (instead of
60 // 1970-01-01 defaults), so `ifNull` cleanly falls back to the profile's
61 // existing values for profiles that have no events.
62 //
63 // `device_id != profile_id` excludes anonymous device-only events from the
64 // lookup (matches the convention used in our other profile/event queries).
65 return `
66 INSERT INTO ${TABLE_NAMES.profiles}
67 (id, is_external, first_name, last_name, email, avatar, properties,
68 project_id, groups, created_at, last_seen_at)
69 SELECT
70 p.id,
71 p.is_external,
72 p.first_name,
73 p.last_name,
74 p.email,
75 p.avatar,
76 p.properties,
77 p.project_id,
78 p.groups,
79 least(p.created_at, ifNull(e.first_seen, p.created_at)) AS created_at,
80 greatest(p.last_seen_at, ifNull(e.last_seen, p.last_seen_at)) AS last_seen_at
81 FROM ${TABLE_NAMES.profiles} AS p FINAL
82 LEFT JOIN (
83 SELECT
84 profile_id,
85 min(created_at) AS first_seen,
86 max(created_at) AS last_seen
87 FROM ${TABLE_NAMES.events}
88 WHERE project_id = {projectId:String}
89 AND profile_id != ''
90 AND device_id != profile_id
91 GROUP BY profile_id
92 ) AS e ON e.profile_id = p.id
93 WHERE p.project_id = {projectId:String}
94 SETTINGS
95 join_use_nulls = 1,
96 -- 'global' rewrites the subquery JOIN to GLOBAL JOIN in cluster mode,
97 -- so the events aggregate is gathered across all shards on the
98 -- initiator and broadcast. Without it, each shard would only see its
99 -- local events slice and the last_seen lookup could undercount. No-op
100 -- in non-clustered setups.
101 distributed_product_mode = 'global',
102 insert_distributed_sync = 1
103 `;
104}
105
106async function backfillProject(
107 projectId: string,

Callers 1

backfillProjectFunction · 0.85

Calls

no outgoing calls

Tested by

no test coverage detected