bkennedy bkennedy - 2 months ago 6
MySQL Question

problems with AVG and LIMIT SQL

So I am asked to find the query: Find average stars awarded by top 20 users in user reviews (most experienced). Compare it with the average stars awarded by bottom 20 users (least expereinced)

the schema is:

Restaurant (name)

Rank (how it was ranked by Trip Advisor, it is not known what their ranking function is!, it is NOT just SCORE)

Score (average stars awarded by reviewers)

User_Name - name of the reviewer/alias

Review_Stars - How many stars did this reviewer give to this restuarant

Review_Date - The date of the review

User_Reviews - Total number of reviews this reviewer gave over his/her history of reviewing for hotels/resturants etc

User_Restaurant_Reviews - Same but just for restaurants

User_Helpful - How many of the user reviews were marked as helpful by other reviewers

So my logic was to select distinct user_names, and their reviews, order that by reviews and limit it to 20. When I went to add the

AVG(REVIEW_STARS)
it completely changes the answers.

SELECT DISTINCT USER_NAME, USER_REVIEWS
FROM TRIPADVISOR
ORDER BY USER_REVIEWS desc
limit 20;


RETURNS:

Dorian C 1329


Scipion-Paris 1219


Waddler 1213


CJTravelerTexas 937


runner-oh... 828


maurowave 818


gabrielct... 720


AJCG 667


pietro r 652


Albia-Newton 651


Jilllian 650


laserkid 648


pstiva 616


lucaheat 585


Chokk 511


Soracte 491


viaggiatr... 487


tornado9 479


Magorzata B 478


Marcello... 464


When I change the query to:

SELECT DISTINCT USER_NAME, USER_REVIEWS, AVG(REVIEW_STARS)
FROM TRIPADVISOR
ORDER BY USER_REVIEWS desc
limit 20;


It returns:
Iain U 60 4.3333


I think my logic must be off in the way I am going about this but if anyone can shed some light it would help

ESG ESG
Answer

You are missing a GROUP BY statement, and you may want to remove the DISTINCT. You need something along the lines of:

SELECT USER_NAME, USER_REVIEWS, AVG(REVIEW_STARS)
FROM TRIPADVISOR
GROUP BY USER_NAME, USER_REVIEWS
ORDER BY USER_REVIEWS desc
limit 20;
Comments