I hope somebody can help me with a tricky Kusto query.

I have the following dataset, which contains events raised by user, where events are represented with event code:

datatable(CreatedDate:datetime, User:string, EventCode:string)
[
    datetime(4-22-2024 12:44:02.750 PM), "user1", "TS",
    datetime(4-23-2024 4:09:30.551 AM), "user1", "TD",
    datetime(4-23-2024 4:09:59.067 AM), "user1", "SP",
    datetime(4-23-2024 7:10:02.052 AM), "user1", "TD",
    datetime(4-23-2024 7:12:05.357 AM), "user1", "TC",
    datetime(4-25-2024 5:11:02.649 AM), "user1", "TD",
    datetime(4-25-2024 5:12:56.672 AM), "user1", "TC",
    datetime(4-23-2024 9:53:12.315 AM), "user2", "TS",
    datetime(4-25-2024 4:36:33.656 AM), "user2", "TD",
    datetime(4-25-2024 4:38:46.922 AM), "user2", "TC",
    datetime(4-22-2024 12:40:35.801 PM), "user3", "TS",
    datetime(4-23-2024 4:13:09.379 AM), "user3", "TD",
    datetime(4-23-2024 4:13:23.724 AM), "user3", "TS",
    datetime(4-23-2024 4:14:23.724 AM), "user3", "TC",
    datetime(4-25-2024 4:34:18.966 AM), "user3", "TD",
    datetime(4-25-2024 4:41:07.381 AM), "user3", "TC",
]
| order by User asc, CreatedDate asc

I need to pivot this data to have a single day per user represented as a single line, which shows all of the complete events with their start and end date on a single line.

Event codes:

• TD – Event Start code
• TC or SP – Event End code
• TS and rest of the codes – Can be ignored

There can be intermediary events between TD and (TC or SP) (which don’t affect anything), or there could be events sequences like TD, TD, TC, in which case only the last sequence should be taken into account.

And event in the output table should be represented by the event end code (TC or SP)

So the expected output for the data above would be this:

There can be many more events raised per day for the user (up to 10-15 events maybe), which would stretch it horizontally.
Also please don’t mind date formatting, it can be anything, I just used the first available one.