Cosmos DB: Efficent way to get count of records by day?

Question

Currently testing on a container that currently has ~20k records (eventually it may have up to 500,000 records per day).

Each document in the container has a "created" property that I'm setting with an ISO 8601 datetime.

I'm trying to find an efficient way of getting the count of records created per day (based on local timezone, not UTC day).

The following query works, but is currently ~1400 RU for retrieving 30 days worth of data:
select
DateTimeAdd("dd", DateTimeDiff("dd", @lookback, c.created), @lookback) as date,
count(1) as count
from
c
where
c.created >= @lookback
group by DateTimeDiff("dd", @lookback, c.created)

I can grab the count for all records within the 30 day period for ~6 RU:
select
count(1) as count
from
c
where
c.created >= @lookback

Is there a more efficient way to get 30 days worth of data than just executing 30 queries to get the data for each individual day (~180 RU)?

Answer 1

Just wanted to add few things here. User Defined Functions RU charges are based on Physical resource usage, hence you are seeing more RUs consumed. Whereas in the 2nd query, you have those individual queries which does not use the User Defined Function will have less RUs because you are just using the Where Clause and Count

Your approach of having different queries executed could be performant than having single query with UDF. Other option is to create materialized views and reading from there would be a good option as well

Answer 2

Except that DateTimeAdd and DateTimeDiff aren't UDFs -- they're system functions:
https://learn.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-datetimeadd