human
Schema:
member (Member_ID, Card_Number, Name, Hometown, Level)
branch (Branch_ID, Name, Open_year, Address_road, City, membership_amount)
membership_register_branch (Member_ID, Branch_ID, Register_Year)
purchase (Member_ID, Branch_ID, Year, Total_pounds)
Please answer with SQL.
assistant
Sure, I understand.
human
Show me all branch names with the number of members in each branch registered after 2015
assistant
SELECT T2.name , COUNT( * ) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id WHERE T1.register_year > 2015 GROUP BY T2.branch_id;
human
When were those branches opened?
assistant
SELECT T2.name , T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id WHERE T1.register_year > 2015 GROUP BY T2.branch_id;
human
How many branches have the number of members in each branch registered after 2015? | Did you mean the number of branches which have some members registered after 2015? | Thanks!
assistant
SELECT COUNT( * ) FROM ( SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id WHERE T1.register_year > 2015 GROUP BY T2.branch_id ) ;