Reporting counts based on date criteria

Hi,

I have this SQL table called UserHistory ingested in ElasticSearch. This table simply stores a record every time a user joins a group and when was that user an active member of that group based on the From and To date columns.

image

If I want to generate a report that would give me the number of users who joined a group since 1 April 2018, I could use this SQL statement

select uh.GroupName, count(1) [UserCount]
from UserHistory u
where '2018-04-18' between u.From and u.To
	and uh.HasLeftGroup = 0
	and not exists 
	(
		select 1 from UserHistory uh2 
			where '2018-04-01' between uh2.From and uh2.To 
				and uh2.UserId = uh.UserId 
				and uh2.HasLeftGroup = 0
	)
group by uh.GroupName

and gives me this result
image

and, if I want to get the number of users who joined a group since 5 April 2018, I would get this result

 select uh.GroupName, count(1) [UserCount]
    from UserHistory u
    where '2018-04-18' between u.From and u.To
    	and uh.HasLeftGroup = 0
    	and not exists 
    	(
    		select 1 from UserHistory uh2 
    			where '2018-04-05' between uh2.From and uh2.To 
    				and uh2.UserId = uh.UserId 
    				and uh2.HasLeftGroup = 0
    	)
    group by uh.GroupName

image

Can we do the same in Kibana?


Cheers
Kenneth

Hey, can do. In the search bar we would put something like From:<=2018-04-05 AND: Tc:>=2018-04-05 AND HasLeftGroup:0 After filtering we would use the data table visualization with a terms aggregation on GroupName.

Thanks for the response - will try this.

Quick question - if you were to model the data before it gets ingested in elastic, is it more suitable to group these historical records into one document per user? Reason I asked because my filters could change depending on the need like say now I am doing a filter on 1 Apr 18, then 5 Apr 18 and so on...

I would like to create a common data table visualization where I can just change the filters

Doc_User1
{
"user_id": 1,
"groups": [
{
"group_name": "Group A",
"from": "2018-03-16 16:30",
"to": "2018-04-05",
"has_left_group": "1"
},
{
"group_name": "Group B",
"from": "2018-04-05 16:30",
"to": "2018-04-18 09:26",
"has_left_group": "0"
}
// ..... etc
]
}

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.