SQL 20250818

import%20marimo%0A%0A__generated_with%20%3D%20%220.17.6%22%0Aapp%20%3D%20marimo.App()%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%20%20%20%20return%20(mo%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20nav_menu%20%3D%20mo.nav_menu(%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2Findex.html%22%3A%20f%22%7Bmo.icon('lucide%3Ahome')%7D%20Home%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2Fnotebooks%2FSQL_20250817.html%22%3A%20f%22%7Bmo.icon('lucide%3Aarrow-big-left')%7D%20Last%20Day%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2Fnotebooks%2FSQL_20250819.html%22%3A%20f%22%7Bmo.icon('lucide%3Aarrow-big-right')%7D%20Next%20Day%22%2C%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20orientation%3D%22horizontal%22%2C%0A%20%20%20%20)%0A%20%20%20%20nav_menu%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20LeetCode%203482%0A%0A%20%20%20%20Table%3A%20Employees%0A%0A%20%20%20%20%7C%20Column%20Name%20%20%20%20%7C%20Type%20%20%20%20%7C%0A%20%20%20%20%7C----------------%7C---------%7C%0A%20%20%20%20%7C%20employee_id%20%20%20%20%7C%20int%20%20%20%20%20%7C%0A%20%20%20%20%7C%20employee_name%20%20%7C%20varchar%20%7C%0A%20%20%20%20%7C%20manager_id%20%20%20%20%20%7C%20int%20%20%20%20%20%7C%0A%20%20%20%20%7C%20salary%20%20%20%20%20%20%20%20%20%7C%20int%20%20%20%20%20%7C%0A%20%20%20%20%7C%20department%20%20%20%20%20%7C%20varchar%20%7C%0A%0A%20%20%20%20employee_id%20is%20the%20unique%20key%20for%20this%20table.%20Each%20row%20contains%20information%20about%20an%20employee%2C%20including%20their%20ID%2C%20name%2C%20their%20manager's%20ID%2C%20salary%2C%20and%20department.%20manager_id%20is%20null%20for%20the%20top-level%20manager%20(CEO).%0A%0A%20%20%20%20Write%20a%20solution%20to%20analyze%20the%20organizational%20hierarchy%20and%20answer%20the%20following%3A%0A%0A%20%20%20%20-%20Hierarchy%20Levels%3A%20For%20each%20employee%2C%20determine%20their%20level%20in%20the%20organization%20(CEO%20is%20level%201%2C%20employees%20reporting%20directly%20to%20the%20CEO%20are%20level%202%2C%20and%20so%20on).%0A%20%20%20%20-%20Team%20Size%3A%20For%20each%20employee%20who%20is%20a%20manager%2C%20count%20the%20total%20number%20of%20employees%20under%20them%20(direct%20and%20indirect%20reports).%0A%20%20%20%20-%20Salary%20Budget%3A%20For%20each%20manager%2C%20calculate%20the%20total%20salary%20budget%20they%20control%20(sum%20of%20salaries%20of%20all%20employees%20under%20them%2C%20including%20indirect%20reports%2C%20plus%20their%20own%20salary).%0A%20%20%20%20-%20Return%20the%20result%20table%20ordered%20by%20the%20result%20ordered%20by%20level%20in%20ascending%20order%2C%20then%20by%20budget%20in%20descending%20order%2C%20and%20finally%20by%20employee_name%20in%20ascending%20order.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20%22%22%22%0A%20%20%20%20WITH%20RECURSIVE%20leadership%20AS%20(%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20manager_id%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20employee_id%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20employee_name%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20salary%2C%0A%20%20%20%20%20%20%20%20%20%20%20%201%20AS%20level%0A%20%20%20%20%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20%20%20%20%20employees%0A%20%20%20%20%20%20%20%20WHERE%0A%20%20%20%20%20%20%20%20%20%20%20%20manager_id%20IS%20NULL%0A%20%20%20%20%20%20%20%20UNION%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20e.manager_id%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20e.employee_id%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20e.employee_name%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20e.salary%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20l.level%2B1%20AS%20level%0A%20%20%20%20%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20%20%20%20%20employees%20e%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20leadership%20l%20ON%20e.manager_id%20%3D%20l.employee_id%0A%20%20%20%20)%2C%0A%20%20%20%20subordinate%20AS%20(%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20employee_id%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20salary%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20manager_id%0A%20%20%20%20%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20%20%20%20%20employees%0A%20%20%20%20%20%20%20%20UNION%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20e.employee_id%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20e.salary%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20s.manager_id%0A%20%20%20%20%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20%20%20%20%20employees%20e%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20subordinate%20s%20ON%20s.employee_id%20%3D%20e.manager_id%0A%20%20%20%20)%0A%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20l.employee_id%2C%0A%20%20%20%20%20%20%20%20l.employee_name%2C%0A%20%20%20%20%20%20%20%20level%2C%0A%20%20%20%20%20%20%20%20COUNT(s.employee_id)%20AS%20team_size%2C%0A%20%20%20%20%20%20%20%20COALESCE(SUM(s.salary)%2C%200)%20%2B%20l.salary%20AS%20budget%0A%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20leadership%20l%0A%20%20%20%20%20%20%20%20%20%20%20%20LEFT%20JOIN%20subordinate%20s%20ON%20s.manager_id%20%3D%20l.employee_id%0A%20%20%20%20GROUP%20BY%0A%20%20%20%20%20%20%20%201%2C2%2C3%2C%0A%20%20%20%20%20%20%20%20l.salary%0A%20%20%20%20ORDER%20BY%0A%20%20%20%20%20%20%20%203%2C%205%20DESC%2C%202%3B%0A%20%20%20%20%22%22%22%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20import%20pandas%20as%20pd%0A%0A%20%20%20%20def%20build_leadership(employees%3A%20pd.DataFrame)%20-%3E%20pd.DataFrame%3A%0A%20%20%20%20%20%20%20%20out%20%3D%20%5B%5D%0A%20%20%20%20%20%20%20%20frontier%20%3D%20employees%5Bemployees%5B%22manager_id%22%5D.isna()%5D.copy()%0A%20%20%20%20%20%20%20%20frontier%5B%22level%22%5D%20%3D%201%0A%20%20%20%20%20%20%20%20out.append(frontier)%0A%0A%20%20%20%20%20%20%20%20while%20not%20frontier.empty%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20nxt%20%3D%20employees.merge(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20frontier%5B%5B%22employee_id%22%2C%20%22level%22%5D%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20left_on%3D%22manager_id%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20right_on%3D%22employee_id%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20how%3D%22inner%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20suffixes%3D(%22%22%2C%20%22_mgr%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20nxt.empty%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20break%0A%20%20%20%20%20%20%20%20%20%20%20%20nxt%20%3D%20nxt.drop(columns%3D%5B%22employee_id_mgr%22%5D)%0A%20%20%20%20%20%20%20%20%20%20%20%20nxt%5B%22level%22%5D%20%3D%20nxt%5B%22level%22%5D%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20out.append(nxt)%0A%20%20%20%20%20%20%20%20%20%20%20%20frontier%20%3D%20nxt%0A%0A%20%20%20%20%20%20%20%20return%20pd.concat(out%2C%20ignore_index%3DTrue)%0A%0A%20%20%20%20def%20build_subordinate(employees%3A%20pd.DataFrame)%20-%3E%20pd.DataFrame%3A%0A%20%20%20%20%20%20%20%20base%20%3D%20employees%5B%5B%22employee_id%22%2C%20%22salary%22%2C%20%22manager_id%22%5D%5D.copy()%0A%20%20%20%20%20%20%20%20results%20%3D%20%5Bbase%5D%0A%20%20%20%20%20%20%20%20frontier%20%3D%20base%5B%5B%22employee_id%22%2C%20%22manager_id%22%5D%5D.copy()%0A%0A%20%20%20%20%20%20%20%20while%20True%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20nxt%20%3D%20employees.merge(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20frontier.rename(columns%3D%7B%22employee_id%22%3A%20%22parent_emp%22%2C%20%22manager_id%22%3A%20%22root_mgr%22%7D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20left_on%3D%22manager_id%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20right_on%3D%22parent_emp%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20how%3D%22inner%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20nxt.empty%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20break%0A%20%20%20%20%20%20%20%20%20%20%20%20step%20%3D%20nxt%5B%5B%22employee_id%22%2C%20%22salary%22%5D%5D.copy()%0A%20%20%20%20%20%20%20%20%20%20%20%20step%5B%22manager_id%22%5D%20%3D%20nxt%5B%22root_mgr%22%5D%0A%20%20%20%20%20%20%20%20%20%20%20%20results.append(step)%0A%20%20%20%20%20%20%20%20%20%20%20%20frontier%20%3D%20step%5B%5B%22employee_id%22%2C%20%22manager_id%22%5D%5D.copy()%0A%0A%20%20%20%20%20%20%20%20return%20pd.concat(results%2C%20ignore_index%3DTrue)%0A%0A%20%20%20%20def%20analyze_organization_hierarchy(employees%3A%20pd.DataFrame)%20-%3E%20pd.DataFrame%3A%0A%20%20%20%20%20%20%20%20ldr%20%3D%20build_leadership(employees)%0A%20%20%20%20%20%20%20%20sub%20%3D%20build_subordinate(employees)%0A%0A%20%20%20%20%20%20%20%20merged%20%3D%20ldr.merge(%0A%20%20%20%20%20%20%20%20%20%20%20%20sub%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20left_on%3D%22employee_id%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20right_on%3D%22manager_id%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20how%3D%22left%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20suffixes%3D(%22%22%2C%20%22_sub%22)%2C%0A%20%20%20%20%20%20%20%20)%0A%0A%20%20%20%20%20%20%20%20out%20%3D%20merged.groupby(%5B%22employee_id%22%2C%20%22employee_name%22%2C%20%22level%22%2C%20%22salary%22%5D%2C%20as_index%3DFalse).agg(%0A%20%20%20%20%20%20%20%20%20%20%20%20team_size%3D(%22employee_id_sub%22%2C%20%22count%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20sum_sub%3D(%22salary_sub%22%2C%20%22sum%22)%2C%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20out%5B%22budget%22%5D%20%3D%20out%5B%22sum_sub%22%5D.fillna(0)%20%2B%20out%5B%22salary%22%5D%0A%20%20%20%20%20%20%20%20out%20%3D%20out.drop(columns%3D%5B%22sum_sub%22%2C%20%22salary%22%5D).sort_values(by%3D%5B%22level%22%2C%20%22budget%22%2C%20%22employee_name%22%5D%2C%20ascending%3D%5BTrue%2C%20False%2C%20True%5D)%0A%20%20%20%20%20%20%20%20return%20out%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A