separately calculated stats side by side in a table

[hyddenlynx]

I am building a table displayed in a splunk dashboard that needs a complicated query and I was hoping to get a quick pointer in the right direction.

Let's say I have a table of events at different levels per person.

id	event_id	level_id	user_id
1	1	1	1
2	1	2	1
3	1	1	2
4	1	2	2
5	1	3	2
6	1	1	3

For these events, only the highest level event is relevant per person. How would I construct a query that shows the following stats, side by side:

1) the total number for each event at each level (3rd column below, I have this part) and 

2) the number of users at a specific event level (4th column below) this is basically the count of the highest level per event per person

The results I'm looking for would look like:

event_id	level_id	total_occured	total_users_at_level
1	1	3	1
1	2	2	1
1	3	1	1

Any help would be greatly appreciated. Thanks!

[somesoni2 · Accepted solution]

Give this a try

your current query that returns fields:  id event_id level_id user_id
| eventstats count as total_occured by level_id
| stats last(event_id) as event_id last(level_id) as level_id last(total_occured) as total_occured by user_id
| stats values(total_occured) as total_occured dc(user_id) as total_users_at_level by event_id level_id