Relevance of Ratings Based on Different Weights by ID

[RKB1923]

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).

[yannK]

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