hello,

i am working on a consumer-review website. in it, my users can write
reviews for given products. in addition, other users can rate any given
review as "Helpful" or "Not Helpful".

how can i write a query to give me the "Most Helpful Review" ? meaning,
on my product page, i want to hilight the review w/ the highest number
of helpful flags.

simplfied tables:


    Products
    --------------------------
    - ProductID


    Reviews
    --------------------------
    - ReviewID
    - ProductID
    - UserID


    ReviewFeedback
    --------------------------
    - ReviewID
    - FeedbackType (1 = helpful, 2 = not-helpful)


....any ideas? someone told me theres a group somewhere that discusses
"Amazon.com" functionality knock-offs; ill continue looking for it as
well.


thanks!
matt

Hi, matt

It depends on your definition of "most helpful":
a) the biggest number of "helpful" feedbacks
b) the highest percentage of "helpful" feedbacks among all feedbacks
c) a combination of the above

I'm assuming that you want the "most helpful" review for a given
product (@ProductID).

First, I would make a view like this:

CREATE VIEWS ReviewsView AS
SELECT *,
  ISNULL(100. * Helpful_Cnt / NULLIF(Helpful_Cnt+NotHelpful_Cnt),0)
  as Percentage
FROM (
  SELECT ReviewID, ProductID, UserID, (
      SELECT COUNT(*) FROM ReviewFeedback f
      WHERE r.ReviewID=f.ReviewID
      AND FeedbackType=1
   ) as Helpful_Cnt, (
      SELECT COUNT(*) FROM ReviewFeedback f
      WHERE r.ReviewID=f.ReviewID
      AND FeedbackType=2
    ) as NotHelpful_Cnt
  FROM Reviews r
) x

For a), you might use:

SELECT * FROM ReviewsView
WHERE ProductID=@ProductID
AND Helpful_Cnt=(
  SELECT MAX(Helpful_Cnt)
  FROM ReviewsView
  WHERE ProductID=@ProductID
)

or:

SELECT TOP 1 WITH TIES * FROM ReviewsView
WHERE ProductID=@ProductID
ORDER BY Helpful_Cnt DESC

For b), replace "Helpful_Cnt" with "Percentage" in the above queries.

Notes:
1. The queries are untested (I hope there are no mistakes in them)
2. If there are more "most helpful" reviews, all of them are returned.
If you only want one, you should add another criteria (like the date of
the review); Another way would be to remove "WITH TIES" from the second
query, to get any of the "most helpful" reviews.
3. When there is only one review and there are no feedbacks for it, the
Percentage returns 0 (instead of NULL, which would have been more
accurrate), so that you get the only review as the "most helpful".
4. Performance may be improved if you compute the Helpful_Cnt,
NonHelpful_Cnt and Percentage columns through triggers or using an
indexed view.
5. The second query (with "TOP 1") should be faster than the first, but
the TOP keyword is a proprietary extension and doesn't work in ANSI
SQL.

Razvan

thanks!

yes. to be more clear, i am attempting to retrieve only one "Most
Helpful" review, per product. my product page hilights the "Most
Helpful" review at the top. if no reviews have been voted as "Helpful",
then the product page doesnt hilight anything and just lists the
reviews as normal.

so that being said... it sounds like i should create the view from
above ("ReviewsView"), and then use one of your queries to get the
ReviewID for the most-helpful?

since the db is MS SQL Server, i am not concerned w/ proprietary SQL
and can go for performance ("TOP 1").

thanks again! i will try this code out next week.


matt

m***@mailinator.com wrote:
Show quoteHide quoteThis query gives the number or helpful feedbacks per review:

SELECT ReviewID,Count(*) Helpfuls
FROM ReviewFeedback
WHERE FeedbackType = 1
GROUP BY ReviewID

You may wish to create a view called HelpfulsPerReview from the above sql.
CREATE VIEW HelpfulsPerReview AS <above sql>

This query gives the max helpfuls per product id:
SELECT ProductID, Max(Helpfuls) MaxHelpfuls
FROM Reviews r INNER JOIN HelpfulsPerReview h
     ON r.ReviewID = h.ReviewID
GROUP BY ProductID

Again, you may wish to create a view called MaxHelpfulsPerProduct out of
this sql.

This query combines the Reviews table with the above views to give you the
review(s)* for each product with the highest helpful feedbacks:


SELECT ProductID, ReviewID, Helpfuls
FROM
    Reviews r
    INNER JOIN HelpfulsPerReview h
        ON r.ReviewID = h.ReviewID
    INNER JOIN MaxHelpfulsPerProduct  m
    ON r.ProductID=m.ProductID AND h.Helpfuls=m.MaxHelpfuls

Again, creating a view from the above query will make it easier to use it in
subsequent queries

*There may be ties, correct?

HTH,
Bob Barrows

hi bob,

....not following on that one. i dont know what ties are, but i do know
im only looking for one most-helpful" review (ID) back.


thanks,
matt

m***@mailinator.com wrote:
You obviously did not try the query. By "ties" i mean the possibility that
two or more review for a product might have the same number of "helpful"
responses, i.e., tied for the "highest helpful feedbacks".

uhhh... nooo, i didnt. im not in the development environment today --
as eluded to in my first reply to razvan. sorry if that's a problem.

anyway, as to the query.. im not going to deal w/ ties -- when it comes
to the showcased review, There Can Be Only One. ill either take the
first one, or designate someplace to go on the most-recent (based on
another column i have for the review-submitted-date).


thanks
matt

m***@mailinator.com wrote:
It's not a problem for me :-)

That's cool. I just wanted you to be aware that the query I suggested would
return more than one review per product in the event of a tie.

Bob Barrows

gotcha... cool, thanks man!


matt

Help developing this project.
Newbie: Timeout On IN statement
Getting an unused number from a set of numbers?
Design: multiple columns for primary key
Whats Wrong with the Query?