1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to get highest rated product of each month from following set?

Discussion in 'MySQL' started by danielwoods2121, Apr 30, 2020.

?

How to get highest rated product of each month from following set?

  1. i dont know

    0 vote(s)
    0.0%
  2. i dont know

    0 vote(s)
    0.0%
  1. #1
    productid average_rating ratings_month
    1 1 1
    1 3 2
    1 5 3
    2 2 1
    2 4 2
    2 1 3
    3 2 1
    3 3 2
    3 4 3
     
    danielwoods2121, Apr 30, 2020 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #2
    In sport you have a countback rule when a competitors points are the same. So on your list you have 3 rated with 1, you'd then look back and only the first product had a 1 rating last month so it'd be the one you pick.

    You can't average a rating that is already an average so if you decided to count more than one month you'd have to query the raw data.

    ========================================

    What's trickier is when you have a new product, or one with fewer sales, and you need to apply relevancy.

    Product A: 1000 sales, 750 ratings, $100 each, average 2.4
    Product B: 100 sales, 50 ratings, $100 each, average 2.1
    Product C: 100 sales, 90 ratings, $1000 each, average 2.5

    Product C costs more and the buyers have been more motivated to leave a rating than both Products A and B.
    Does Product B deserve to be the top product when so few people buy it?
     
    sarahk, Apr 30, 2020 IP
  3. danielwoods2121

    danielwoods2121 Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3


    I am sorry. I think I asked my question in wrong way. What I actually want to do is to group by monthes and get the highest amount from average_rating column along with the productId currently I am using following query but It gives me wrong informations since it returns productId 1 for all monthes:

    select productid,max(average_rating),ratings_month from rating group by ratings_month.

    above query groups data buy monthes and get highest average_rating for each month but returns wrong productId. Do you know what I am doing wrong here?
     
    danielwoods2121, Apr 30, 2020 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #4
    aaah, gotcha, yep, that's not an easy query.

    I'd start with something like this https://www.db-fiddle.com/f/tLcy2nfqUdFizExhpxktRP/0
    select ratings_month, max(average_rating) as best_rank
    from ratings 
    group by ratings_month
    order by ratings_month
    Code (markup):
    and then go back and find the products that had that rating.
    It's not perfect but easier to maintain when you revisit the code and a few extra queries isn't the end of the world
     
    sarahk, Apr 30, 2020 IP
  5. danielwoods2121

    danielwoods2121 Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #5

    Thank you. So its not possible to get product_id and these data together with same query?
     
    danielwoods2121, Apr 30, 2020 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #6
    There's a way, you can google for examples but I can't remember off the top of my head. I have that situation too and I have to hunt down the answer every time it pops up. If you're not confident with MySQL it can be better just to do it the long way.
     
    sarahk, Apr 30, 2020 IP
  7. danielwoods2121

    danielwoods2121 Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #7
    I
     
    danielwoods2121, Apr 30, 2020 IP