use cosc_61;
DROP TABLE IF EXISTS employee;
create table employee ( employee_id DECIMAL(4,0), first_name VARCHAR(255), last_name VARCHAR(255), dept_id VARCHAR(255), manager_id VARCHAR(255), salary DECIMAL(6,0), expertise VARCHAR(255));

INSERT INTO employee VALUES (100,"John","White","IT",103,120000,"Senior");
INSERT INTO employee VALUES (101,"Mary","Danner","Account",109,80000,"Junior");
INSERT INTO employee VALUES (102,"Ann","Lynn","Sales",107,140000,"Semisenior");
INSERT INTO employee VALUES (103,"Peter","Oconnor","IT",110,130000,"Senior");
INSERT INTO employee VALUES (106,"Sue","Sanchez","Sales",107,110000,"Junior");
INSERT INTO employee VALUES (107,"Marta","Doe","Sales",110,180000,"Senior");
INSERT INTO employee VALUES (109,"Ann","Danner","Account",110,90000,"Senior");
INSERT INTO employee VALUES (110,"Simon","Yang","CEO",null,250000,"Senior");
INSERT INTO employee VALUES (111,"Juan","Graue","Sales",102,37000,"Junior");
SELECT * FROM employee;

SELECT 
  employee_id,
  last_name,
  first_name,
  salary,
  RANK() OVER (ORDER BY salary DESC) as ranking
FROM employee
ORDER BY ranking;

-- 
WITH employee_ranking AS (
  SELECT
    employee_id,
    last_name,
    first_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  employee_id,
  last_name,
  first_name,
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking;

--

WITH employee_ranking AS (
  SELECT
    employee_id,
    last_name,
    first_name,
    salary,
    RANK() OVER (ORDER BY salary ASC) as ranking
  FROM employee
)
SELECT
  employee_id,
  last_name,
  first_name,
  salary
FROM employee_ranking
WHERE ranking <= 5
ORDER BY ranking;

--

WITH employee_ranking AS (
  SELECT
    employee_id,
    last_name,
    first_name,
    salary,
    dept_id,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as ranking
  FROM employee
)
SELECT
  dept_id,
  employee_id,
  last_name,
  first_name,
  salary
FROM employee_ranking
WHERE ranking = 1
ORDER BY dept_id, last_name;

-- 

WITH employee_ranking AS (
  SELECT
    employee_id,
    last_name,
    first_name,
    salary,
    NTILE(2) OVER (ORDER BY salary ) as n2tile
  FROM employee
)
SELECT
  employee_id,
  last_name,
  first_name,
  salary
FROM employee_ranking
WHERE n2tile = 1
ORDER BY salary ;

-- 

WITH employee_ranking AS (
  SELECT
    employee_id,
    last_name,
    first_name,
    salary,
    NTILE(4) OVER (ORDER BY salary) as n4tile
  FROM employee
)
SELECT
  employee_id,
  last_name,
  first_name,
  salary
FROM employee_ranking
WHERE n4tile = 4
ORDER BY salary ;

-- 

SELECT
  employee_id,
  last_name,
  first_name,
  salary,
  ROW_NUMBER() OVER (ORDER BY employee_id) as ranking_position
FROM employee;

--

SELECT 
  CONCAT(e1.first_name,' ',e1.last_name) AS manager_name,
  CONCAT(e2.first_name,' ',e2.last_name) AS employee_name
FROM employee e1
JOIN employee e2
ON e1.employee_id = e2.manager_id;

--

SELECT
  first_name,
  last_name,
  salary
FROM employee 
WHERE salary > ( SELECT AVG(salary) FROM employee );

--

SELECT
  first_name,
  last_name,
  salary
FROM employee AS e1
WHERE salary >
    (SELECT AVG(salary)
     FROM employee AS e2
     WHERE e1.dept_id = e2.dept_id);
     
--

-- >>> FAILS; need to get to manager_name
SELECT 
  first_name,
  last_name
FROM employee AS e1
WHERE dept_id IN (
   SELECT dept_id
   FROM employee
   WHERE manager_name='John Smith');

-- 

INSERT INTO employee VALUES (103,"Peter","Oconnor","IT",110,130000,"Senior");


SELECT 
  employee_id,
  last_name,
  first_name,
  dept_id,
  manager_id,
  salary
FROM employee
GROUP BY   
  employee_id,
  last_name,
  first_name,
  dept_id,
  manager_id,
  salary
HAVING COUNT(*) > 1;
SELECT * FROM employee;
DELETE FROM employee WHERE employee_id = 103 limit 1;
SELECT * FROM employee;

--

SELECT 
  employee_id,
  last_name,
  first_name,
  dept_id,
  manager_id,
  salary,
  COUNT(*) AS number_of_rows
FROM employee
GROUP BY
  employee_id,
  last_name,
  first_name,
  dept_id,
  manager_id,
  salary
HAVING COUNT(*) > 1;

--
-- 19
SELECT
  CASE
    WHEN salary <= 75000 THEN 'low'
    WHEN salary > 75000 AND salary <= 100000 THEN 'medium'
    WHEN salary > 100000 THEN 'high'
  END AS salary_category,
  COUNT(*) AS number_of_employees
FROM    employee
GROUP BY
  CASE
    WHEN salary <= 75000 THEN 'low'
    WHEN salary > 75000 AND salary <= 100000 THEN 'medium'
    WHEN salary > 100000 THEN 'high'
END;
SELECT * FROM employee;

-- 
-- 22

DROP TABLE IF EXISTS `sales`;

CREATE TABLE `sales` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `Day` varchar(255),
  `Amount` varchar(100) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `sales` (`Day`,`Amount`)
VALUES
  ("Jun 8, 2025","$48.94"),
  ("Apr 4, 2024","$74.39"),
  ("Dec 12, 2024","$90.57"),
  ("Jul 22, 2025","$72.15"),
  ("Jan 5, 2026","$47.31"),
  ("Mar 17, 2024","$57.31"),
  ("Jul 15, 2024","$1.33"),
  ("Jun 14, 2025","$46.56"),
  ("May 13, 2025","$74.57"),
  ("Jan 1, 2026","$74.51");
INSERT INTO `sales` (`Day`,`Amount`)
VALUES
  ("Sep 19, 2024","$64.94"),
  ("Jun 8, 2024","$34.82"),
  ("Jan 31, 2026","$48.77"),
  ("Nov 28, 2025","$11.05"),
  ("Jul 11, 2025","$39.17"),
  ("Jan 24, 2026","$71.59"),
  ("Nov 2, 2024","$99.94"),
  ("Feb 3, 2025","$11.94"),
  ("Jan 13, 2026","$90.30"),
  ("May 19, 2024","$20.56");

select * from sales;
SELECT
  day,
  amount,
  amount - LAG(amount) OVER (ORDER BY day)
    AS delta_yesterday_today
FROM sales;

--
-- 24

WITH RECURSIVE subordinate AS (
 SELECT 
   employee_id,
   first_name,
   last_name,
   manager_id
  FROM employee
  WHERE employee_id = 110 -- id of the top hierarchy employee (CEO)
  
  UNION ALL
  
  SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id
  FROM employee e
  JOIN subordinate s
  ON e.manager_id = s.employee_id
)
SELECT 
  employee_id,
  first_name,
  last_name,
  manager_id
FROM subordinate ;

