Kusto - Grouping by week, Week-ending

Question

I come up against this quite often and haven't figured it out yet. Take the below query. I am trying to group into 7 day buckets, however the first and last bucket are always less than 7 days. The middle buckets are whole weeks ( or 6.23 days whatever that means).

How do I write a query where I can offset by the end date? Additionally, how can I make sure my start date is also not truncated?

requests
| where timestamp > startofday(ago(90d))
        and timestamp < endofday(now()-1d)
| summarize 
        min(timestamp),
        max(timestamp)
        by 
        bin(timestamp, 7d)
| extend duration = max_timestamp - min_timestamp
| project-away timestamp
| order by max_timestamp

Answer 1

You can use bin_at() to specify the reference data for the binning. See example below, and documentation: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/binatfunction.

If it is relevant, you could also consider using startofweek() and/or endofweek().

range timestamp from startofday(ago(30d)) to endofday(ago(1d)) step 1111ms
| summarize max(timestamp), min(timestamp) by timestamp = bin_at(timestamp, 7d, endofday(ago(1d)))
| extend duration = max_timestamp - min_timestamp
| project-away timestamp
| order by max_timestamp

-->

| max_timestamp               | min_timestamp               | duration           |
|-----------------------------|-----------------------------|--------------------|
| 2020-06-25 23:59:59.6630000 | 2020-06-19 00:00:00.1490000 | 6.23:59:59.5140000 |
| 2020-06-18 23:59:59.0380000 | 2020-06-12 00:00:00.6350000 | 6.23:59:58.4030000 |
| 2020-06-11 23:59:59.5240000 | 2020-06-05 00:00:00.0100000 | 6.23:59:59.5140000 |
| 2020-06-04 23:59:58.8990000 | 2020-05-29 00:00:00.4960000 | 6.23:59:58.4030000 |
| 2020-05-28 23:59:59.3850000 | 2020-05-27 00:00:00.0000000 | 1.23:59:59.3850000 |