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.
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
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
Can we do the same in Kibana?
Cheers
Kenneth