Converting an SQL query to Kusto query language

Question

Need help with converting SQL code below to kusto query language

SELECT edate , employeename, flag, SUM(CASE WHEN flag = 'yes' THEN 1 ELSE 0 END) OVER (PARTITION BY employeename ORDER BY edate) AS sessionid FROM test;

Business outcome needed

1)Consider table with fields edate datetime, employeename varchar(50), flag(yes/No) present in table.

Need a new field named session id that should start with 1 for each employee and increment by 1 when flag field has value 1.

Eg data

edate , employeename ,flag,sessionid(OUTPUT column expected)

10-Jul-2023 10am,MrX,Y,1

10-Jul-2023 11am,MrX,N,1

10-Jul-2023 12pm,MrX,Y,2

10-Jul-2023,1pm,MrX,Y,3

10-Jul-2023,MrY,Y,1

10-Jul-2023,MrX,N,3

10-Jul-2023,MrY,N,1

10-Jul-2023,MrY,N,1

10-Jul-2023,MrY,N,2

10-Jul-2023,MrX,Y,4

The number is what is to be derived in kusto query language and i want to return entire table with the 4 columns above

Chatgpt gives this but doesnt seem to work for me.

test | extend sessionid = iff(flag == 'yes', 1, 0) | extend sessionid = sum(sessionid) over (partition by employeename order by edate) | project edate, employeename, flag, sessionid

Answer 1

Hi,

Thanks for reaching out to Microsoft Q&A.

This is what microsoft documentation has mentioned for converting SQL to KQL.

If you're familiar with SQL and want to learn KQL, translate SQL queries into KQL by prefacing the SQL query with a comment line, --, and the keyword explain. The output will show the KQL version of the query, which can help you understand the KQL syntax and concepts.

Please go through the cheat sheet in the below link from microsoft.
https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/sqlcheatsheet

Let me know if this worked, so we can assure other users who are looking for such solution.

Please Upvote and Accept as answer if the reply was helpful, this will be benefitting the other community members who go through the same issue.