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 all the tips from a user who has written a review in 2012
assistant
SELECT t2.text FROM USER AS t3 JOIN review AS t1 ON t3.user_id = t1.user_id JOIN tip AS t2 ON t3.user_id = t2.user_id WHERE t1.year = 2012;
human
find the number of reviews written for ' Cafe Zinho ' restaurant in Texas
assistant
SELECT COUNT ( DISTINCT t3.text ) FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id JOIN review AS t3 ON t3.business_id = t1.business_id WHERE t1.name = 'Cafe Zinho' AND t1.state = 'Texas' AND t2.category_name = 'restaurant';
human
Find the number of reviews on businesses located in ' South Summerlin ' neighbourhood
assistant
SELECT COUNT ( DISTINCT t3.text ) FROM neighbourhood AS t1 JOIN business AS t2 ON t1.business_id = t2.business_id JOIN review AS t3 ON t3.business_id = t2.business_id WHERE t1.neighbourhood_name = 'South Summerlin';
human
What is the average rating of reviews written in year 2014 ?
assistant
SELECT AVG ( rating ) FROM review WHERE YEAR = 2014;
human
Find the business with the most number of reviews in April
assistant
SELECT t1.name FROM review AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id WHERE t2.month = 'April' GROUP BY t1.name ORDER BY COUNT ( DISTINCT t2.text ) DESC LIMIT 1;
human
Find the total number of reviews written in March
assistant
SELECT COUNT( DISTINCT text ) FROM review WHERE MONTH = "March" ;