Get unique user count based on time slab

Hi Experts,

I have one use case of time bucket slabs count

I want a count of users who come more than x time in multiple time slab.

e.g.

{“uid”:“user1”, “datetime”:“2017-10-11 12:23:32”}

{“uid”:“user2”, “datetime”:“2017-10-11 12:24:32”}

{“uid”:“user3”, “datetime”:“2017-10-11 12:25:32”}

{“uid”:“user2”, “datetime”:“2017-10-11 13:23:32”}

{“uid”:“user1”, “datetime”:“2017-10-11 13:23:32”}

{“uid”:“user1”, “datetime”:“2017-10-11 13:24:32”}

I want if one user comes multiple times within 1 hour but it should count 1

my expected result who comes 2 or more than 2 -> “result”:{“unique_count” : 2}

it contains user1 and user2. For user1 it comes twice in 13th hour but we should count it 1.

Do we have these kinds of the query of a solution in druid?

I could do the nested query to get numbers of users come more than x time but I am not able to find how I can check time for the perticular interval.

nested query:

{

“queryType”: “groupBy”,

“intervals”: [

“2017-10-09T00:00:00.000Z/2017-10-10T00:00:00.000Z”

],

“granularity”: “all”,

“dimensions”: [

“freq_count”

],

“aggregations”: [{

“type”: “count”,

“name”: “unique_user”,

“fieldName”: “numz”

}],

“filter”: {

“type”: “and”,

“fields”: [

{

“type”: “bound”,

“dimension”: “freq_count”,

“lower”: “2”

}

]

},

“dataSource”: {

“type”: “query”,

“query”: {

“queryType”: “groupBy”,

“dataSource”: “user_event_analytics”,

“granularity”: “hour”,

“dimensions”: [

“uid”

],

“aggregations”: [{

“type”: “longSum”,

“name”: “freq_count”,

“fieldName”: “numz”

}],

“intervals”: [

“2017-10-09T00:00:00.000Z/2017-10-10T00:00:00.000Z”

]

}

}

}

It will give result like :

{

“version”: “v1”,

“timestamp”: “2017-10-09T00:00:00.000Z”,

“event”: {

“freq_count”: “1”, (user3 comes user this)

“unique_user”: 1

}

},

“version”: “v1”,

“timestamp”: “2017-10-09T00:00:00.000Z”,

“event”: {

“freq_count”: “2”, (user2 comes user this)

“unique_user”: 1

}

},

"version": "v1",

“timestamp”: “2017-10-09T00:00:00.000Z”,

“event”: {

“freq_count”: “3”, (user1 comes user this)

“unique_user”: 1

}

}

To solve this time slab solution I was thinking to apply histogram buckets for one hour millis on the basis of timemillis, but I don’t know how I will get exact numbers.

Please, someone, suggest me the solution on this.

Thanks,

Jitesh