I’m trying to join two log tables with clickhouse to get one table with all events and ordered by event_date.
The first table
| aws_id | zip | is_mail_approved | created_at | updated_at |
|---|---|---|---|---|
| 54 | 0 | 0 | 2021-06-18 21:05:11 | 2023-08-08 16:18:29 |
| 54 | 317317 | 1 | 2021-06-18 21:05:11 | 2023-09-19 09:30:36 |
| 54 | 317317 | 1 | 2021-06-18 21:05:11 | 2023-09-19 09:30:36 |
the second table
| aws_id | aws_exporter_id | updated_at |
|---|---|---|
| 54 | 5 | 2023-08-08 16:21:41 |
| 54 | 501 | 2023-09-19 09:37:42 |
And result table
| aws_id | zip | is_mail_approved | created_at | aws_exporter_id | updated_at |
|---|---|---|---|---|---|
| 54 | 0 | 0 | 2021-06-18 21:05:11 | 0 | 2023-08-08 16:18:29 |
| 54 | 0 | 0 | 2021-06-18 21:05:11 | 5 | 2023-08-08 16:21:41 |
| 54 | 317317 | 0 | 2021-06-18 21:05:11 | 5 | 2023-08-09 14:14:15 |
| 54 | 317317 | 1 | 2021-06-18 21:05:11 | 5 | 2023-09-19 09:30:36 |
| 54 | 317317 | 1 | 2021-06-18 21:05:11 | 501 | 2023-09-19 09:37:42 |
I tried using asof join with <= and >= but the result is not correct