Is there way I can do a "for loop" in Kusto query?

Question

Context: I'm trying to compute Daily Active Users/Monthly Active users(last 30days). For eg: if my Daily active users on 1st May 2022 is 1200, and Monthly active users for last days is 1600 (i.e. Last 30 days = 1st April 2022 to 1st May 2022). And I want this data rolling for up to 30 days in the past. More explanation below.

Current query: I'm able to get Daily active users and Monthly active users(last 30 days) for today's date(using now()) from below query.

Events
| AccountId == 'xyz'
| where timestamp between ((now()-31d)..(now()-1d))
| summarize MonthyDCount = dcount(userId) by AccountId, To = To=format_datetime((now()-1d),"dd-MM-yyyy"),From=format_datetime((now()-31d),"dd-MM-yyyy")
| join (Events
         | AccountId = 'xyz'
         | timestamp > ago(3d)
         | summarize dailyDcount = dcount(userId) by AccountId, DAUDate=format_datetime(timestamp,"dd-MM-yyyy"))
on ($left.AccountId == $right.AccountId) and ($left.To == $right.DAUDate)
| project AccountId, To, From, DAUDate, MonthlyDcount, dailyDcount

Current Result:

AccountID	To	From	DAUDate	MonthlyDCount	dailyDCount
xyz	01-05-2022	01-04-2022	01-05-2022	1600	1200

Expected Results:

AccountID	To	From	DAUDate	MonthlyDCount	dailyDCount
xyz	01-05-2022	01-04-2022	01-05-2022	1600	1200
xyz	30-04-2022	31-03-2022	30-04-2022	1580	1250
xyz	29-04-2022	30-03-2022	29-03-2022	1676	1499
xyz	28-04-2022	29-03-2022	28-04-2022	1560	1295
...

Is it possible to achieve this in Kusto??

Thanks!

Answer 1

I'm trying to compute Daily Active Users/Monthly Active users(last 30days).

You can consider using the built-in User analytics plugins.

Is there way I can do a "for loop" in Kusto query?

It's not directly related to the content of your questions, but as this is the title of your question then i'll answer - you can achieve that using the partition operator.