Hi, beginner here having problems trying to write a query.
In my data, I have an event that records when an app is rated (the event records id and rating from one to five stars). I want to weigh the ratings like so:
5-star or 1-star = important (2 point)
4-star or 2-star = semi_important (1.5 points)
3-star = not_important (1 points)
I'm weighing the star-ratings unequally as I believe those who are extremely satisfied/dissatisfied will tend to be more vocal and those having average experiences will not be. I want the query to return the apps that need the most attention, good or bad.
Based on those points, I'd like to create a relevance score:
relevance = important + semi_important + not_important
I'd like to return a table with the following columns:
id, rating, average_rating, relevance
I can get the first three columns with the following, but don't know how to get my relevance score:
evt_name="RateApplet" | stats count("evt_name") as times_rated avg("rating") as average_rating by applet_id
Thank you for any guidance anyone can give me (or any suggestions to improve).
for each app, do a count by rating.
evt_name="RateApplet" | stats count("evt_name") by applet_id rating
it will return something like
applet_id | rating | count
Then you can use an nice eval function to calculate your weight (or define a macro)
| eval rating_weight=case(rating=3 OR rating=4,1.5, rating=5 OR rating=1,2,1=1,0)
| eval weight=count*rating_weight
And finally do another stat to sum the weight per app.
| stats sum(weight) by applet_id
the total search looks like,
evt_name="RateApplet" | stats count("evt_name") by applet_id rating
| eval rating_weight=case(rating=3 OR rating=4,1.5, rating=5 OR rating=1,2,1=1,0)
| eval weight=count*rating_weight
| stats sum(weight) by applet_id