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
Could you please show the membership level with the greatest number of members?
assistant
SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY COUNT( * ) DESC LIMIT 1;
human
What are the member ids of the people who have that membership level?
assistant
SELECT Member_ID FROM member WHERE level IN ( SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY COUNT( * ) DESC LIMIT 1 );
human
What are their names, please?
assistant
SELECT Name FROM member WHERE level IN ( SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY COUNT( * ) DESC LIMIT 1 );
human
What about the membership level with the least number of members?
assistant
SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY COUNT( * ) ASC LIMIT 1;
human
What are the member ids of the people who have that membership level?
assistant
SELECT member_id FROM member WHERE level IN ( SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY COUNT( * ) ASC LIMIT 1 ) ;