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 the average number of checkins in restaurant ' Barrio Cafe ' per day
assistant
SELECT AVG ( t3.count ) , t3.day FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id JOIN checkin AS t3 ON t3.business_id = t1.business_id WHERE t1.name = 'Barrio Cafe' AND t2.category_name = 'restaurant' GROUP BY t3.day;
human
Find the total checkins in ' Brighton Heights ' neighbourhood
assistant
SELECT SUM ( t3.count ) FROM checkin AS t3 JOIN business AS t1 ON t3.business_id = t1.business_id JOIN neighbourhood AS t2 ON t2.business_id = t1.business_id WHERE t2.neighbourhood_name = 'Brighton Heights';
human
find the total checkins in Moroccan restaurant in ' Los Angeles '
assistant
SELECT SUM ( t4.count ) FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id JOIN category AS t3 ON t3.business_id = t1.business_id JOIN checkin AS t4 ON t4.business_id = t1.business_id WHERE t1.city = 'Los Angeles' AND t2.category_name = 'restaurant' AND t3.category_name = 'Moroccan';
human
find the total checkins in Moroccan restaurant in ' Los Angeles ' on Friday
assistant
SELECT SUM ( t4.count ) FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id JOIN category AS t3 ON t3.business_id = t1.business_id JOIN checkin AS t4 ON t4.business_id = t1.business_id WHERE t1.city = 'Los Angeles' AND t2.category_name = 'Moroccan' AND t3.category_name = 'restaurant' AND t4.day = 'Friday';
human
find the total checkins in Moroccan restaurant in ' Los Angeles ' per day
assistant
SELECT t4.day , SUM ( t4.count ) FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id JOIN category AS t3 ON t3.business_id = t1.business_id JOIN checkin AS t4 ON t4.business_id = t1.business_id WHERE t1.city = 'Los Angeles' AND t2.category_name = 'Moroccan' AND t3.category_name = 'restaurant' GROUP BY t4.day;
human
find the total checkins in Italian Delis in each state on Sunday
assistant
SELECT t1.state , SUM( t4.count ) FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id JOIN category AS t3 ON t3.business_id = t1.business_id JOIN checkin AS t4 ON t4.business_id = t1.business_id WHERE t2.category_name = "Italian" AND t3.category_name = "Delis" AND t4.day = "Sunday" GROUP BY t1.state ;