New_Companies.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# New Companies\r\n",
                "\r\n",
                "[Problem Link on Hackerrank](https://www.hackerrank.com/challenges/the-company/problem)\r\n",
                "\r\n",
                "\r\n",
                "Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:\r\n",
                "- Founder\r\n",
                "- Lead Manager\r\n",
                "- Senior Manager\r\n",
                "- Manager\r\n",
                "- Employee\r\n",
                "\r\n",
                "Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.\r\n",
                "\r\n",
                "Note:\r\n",
                "\r\n",
                "- The tables may contain duplicate records.\r\n",
                "- The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.\r\n",
                "\r\n",
                "Input Format\r\n",
                "\r\n",
                "The following tables contain company data:\r\n",
                "\r\n",
                "Company: The company_code is the code of the company and founder is the founder of the company.\r\n",
                "\r\n",
                "| Column | Type |\r\n",
                "| --- | --- |\r\n",
                "| company_code | String |\r\n",
                "| founder | String |\r\n",
                "\r\n",
                "Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company.\r\n",
                "\r\n",
                "| Column | Type |\r\n",
                "| --- | --- |\r\n",
                "| lead_manager_code | String |\r\n",
                "| company_code | String |\r\n",
                "\r\n",
                "Senior_Manager: The senior_manager_code is the code of the senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.\r\n",
                "\r\n",
                "| Column | Type |\r\n",
                "| --- | --- |\r\n",
                "| senior_manager_code | String |\r\n",
                "| lead_manager_code | String |\r\n",
                "| company_code | String |\r\n",
                "\r\n",
                "Manager: The manager_code is the code of the manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.\r\n",
                "\r\n",
                "| Column | Type |\r\n",
                "| --- | --- |\r\n",
                "| manager_code | String |\r\n",
                "| senior_manager_code | String |\r\n",
                "| lead_manager_code | String |\r\n",
                "| company_code | String |\r\n",
                "\r\n",
                "Employee: The employee_code is the code of the employee, the manager_code is the code of its manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.\r\n",
                "\r\n",
                "| Column | Type |\r\n",
                "| --- | --- |\r\n",
                "| employee_code | String |\r\n",
                "| manager_code | String |\r\n",
                "| senior_manager_code | String |\r\n",
                "| lead_manager_code | String |\r\n",
                "| company_code | String |\r\n",
                "\r\n",
                "## Solution:"
            ],
            "metadata": {
                "azdata_cell_guid": "eba47e04-a54a-49a7-91aa-6b9f2ff0df13"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT c.company_code\r\n",
                "    , c.founder\r\n",
                "    , count(DISTINCT (lm.lead_manager_code))\r\n",
                "    , count(DISTINCT (sm.senior_manager_code))\r\n",
                "    , count(DISTINCT (m.manager_code))\r\n",
                "    , count(DISTINCT (e.employee_code))\r\n",
                "FROM Company c\r\n",
                "    , Lead_Manager lm\r\n",
                "    , Senior_Manager sm\r\n",
                "    , Manager m\r\n",
                "    , Employee e\r\n",
                "WHERE c.company_code = lm.company_code\r\n",
                "    AND lm.lead_manager_code = sm.lead_manager_code\r\n",
                "    AND sm.senior_manager_code = m.senior_manager_code\r\n",
                "    AND m.manager_code = e.manager_code\r\n",
                "GROUP BY c.company_code\r\n",
                "    , c.founder\r\n",
                "ORDER BY c.company_code ASC;"
            ],
            "metadata": {
                "azdata_cell_guid": "6f9fcccf-2039-4e9f-abf1-1f96ee5a1f83",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}