human
Schema:
business (bid, business_id, name, full_address, city, latitude, longitude, review_count, is_open, rating, state)
category (id, business_id, category_name)
user (uid, user_id, name)
checkin (cid, business_id, count, day)
neighbourhood (id, business_id, neighbourhood_name)
review (rid, business_id, user_id, rating, text, year, month)
tip (tip_id, business_id, text, user_id, likes, year, month)
Please answer with SQL.
assistant
Sure, I understand.
human
Find users whose average review rating is below 3
assistant
SELECT t2.name FROM USER AS t2 JOIN review AS t1 ON t2.user_id = t1.user_id GROUP BY t2.name HAVING AVG ( t1.rating ) < 3;
human
List all the reviews which rated a business less than 1
assistant
SELECT text FROM review WHERE rating < 1;
human
Find all reviews for businesses rated 2.5
assistant
SELECT t2.text FROM review AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id WHERE t1.rating = 2.5;
human
How many reviews has Michelle written in March 2014 ?
assistant
SELECT COUNT ( DISTINCT t1.text ) FROM USER AS t2 JOIN review AS t1 ON t2.user_id = t1.user_id WHERE t1.month = 'March' AND t1.year = 2014 AND t2.name = 'Michelle';
human
Find the total number of reviews written in March
assistant
SELECT COUNT ( DISTINCT text ) FROM review WHERE MONTH = 'March';
human
What is the average rating of reviews written in year 2014 ?
assistant
SELECT AVG ( rating ) FROM review WHERE YEAR = 2014 ;