Fast search for ratio of users who connect 3 days or more to all users within a week

[fuzzy_rocks]

I want to generate ratio of Frequent Users / Total Users in a particular week. Frequent users are people who come into our system on 4 individual days or more (irrespective of the amount of activity). The idea is that I can trend the value over a few weeks.
I have a search that works, but is very convoluted a somewhat slow. Hoping someone knows a more efficient way to do it. FYI I use __time because date_mday returns 8 values for a week, most likely because of how timezones are set up.

The query I have is
......| eval mydatemday = strftime(_time, "%d")| stats dc(mydatemday) AS DaysConnected by UserId | stats count(eval(DaysConnected<=4)) AS Occasionalusers count(eval(DaysConnected>4)) AS FrequentUsers | eval UserLoyalty = FrequentUsers*100 /(FrequentUsers+Occasionalusers)

[HiroshiSatoh · Accepted solution]

Are not considered dramatic improvement because it is processing "ALL-> Users" heavy
(input->output Number)
| eval mydatemday = strftime......ALL->ALL <= unnecessary if you use the date_mday
| stats dc(mydatemday) AS Da......ALL->Users <= Heavy
| stats count(eval(DaysConne......Users->1
| eval UserLoyalty = Frequen......1->1

Summary Index is useful when you want to find a large amount of Data on a regular basis, you want to see results quickly.

[HiroshiSatoh]

Check the information in the search job inspector(i button).
earliest?
latest?

[earliest=-7d@d latest=@d] will be 8 day?

[fuzzy_rocks]

Thank you!. I couldn't use date_mday as it doesn't respond correctly - e.g. it will return data for 8 days when I ask for a week. I believe it has to do with time zones.

Looks like indexes is way to go.....