human
Schema:
buildings (id, name, City, Height, Stories, Status)
Companies (id, name, Headquarters, Industry, Sales_billion, Profits_billion, Assets_billion, Market_Value_billion)
Office_locations (building_id, company_id, move_in_year)
Please answer with SQL.
assistant
Sure, I understand.
human
Which company has the most buildings? | Do you want to the name of the company? | Yes and the company ID.
assistant
SELECT name , id FROM companies WHERE id = ( SELECT company_id FROM office_locations GROUP by company_id ORDER by COUNT( * ) desc LIMIT 1 );
human
What is the building ID that has the most company offices?
assistant
SELECT T2.id FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id = T2.id JOIN Companies AS T3 ON T1.company_id = T3.id GROUP BY T1.building_id ORDER BY COUNT( * ) DESC LIMIT 1;
human
What is the name of building with ID 3?
assistant
SELECT name FROM buildings WHERE id = "3" ;