| 56 | ); |
| 57 | |
| 58 | function 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 | |
| 106 | async function backfillProject( |
| 107 | projectId: string, |