human
Schema:
follows (f1, f2)
tweets (id, uid, text, createdate)
user_profiles (uid, name, email, partitionid, followers)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the average number of followers for the users who had some tweets.
assistant
SELECT avg(followers) FROM user_profiles WHERE UID IN (SELECT UID FROM tweets)
human
Find the number of tweets in record.
assistant
SELECT count(*) FROM tweets
human
Find the names of users who have more than one tweet.
assistant
SELECT T1.name FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid = T2.uid GROUP BY T2.uid HAVING count(*) > 1
human
Find the name of the user who tweeted more than once, and number of tweets tweeted by them.
assistant
SELECT T1.name , count(*) FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid = T2.uid GROUP BY T2.uid HAVING count(*) > 1
human
Find the name of each user and number of tweets tweeted by each of them.
assistant
SELECT T1.name , count(*) FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid = T2.uid GROUP BY T2.uid
human
Find the number of users who posted some tweets.
assistant
SELECT COUNT( DISTINCT UID ) FROM tweets ;