Window Function and Recurisve Queries

DROP TABLE seq;
CREATE TABLE seq(x INT );
INSERT INTO seq VALUES(1);
INSERT INTO seq VALUES(1);
INSERT INTO seq VALUES (1);
INSERT INTO seq VALUES(2);
INSERT INTO seq VALUES(2);
INSERT INTO seq VALUES(3);
INSERT INTO seq VALUES(3);
INSERT INTO seq VALUES(4);

SELECT v, SUM(v) OVER ()  FROM seq;

SELECT v, SUM(v) OVER w  FROM seq
WINDOW w AS ()

SELECT v, SUM(v) OVER w   FROM seq
WINDOW w AS (RANGE BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW)

SELECT *, SUM(x) OVER w  FROM seq
window w AS (ORDER BY x);

SELECT *, SUM(x) OVER w  FROM seq
window w AS ( ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

SELECT *, SUM(x) OVER w  FROM seq
window w AS ( ORDER BY x  ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING);

SELECT *, SUM(x) OVER w  FROM seq
window w AS ( ORDER BY x  RANGE BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING);

SELECT *, row_number() OVER w FROM seq
window w AS (   );

SELECT *, rank() OVER  (ORDER BY x),
 row_number() OVER (ORDER BY x)
 FROM seq;

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
product CHAR(10),
y INT,
sales DECIMAL(10,2));

INSERT INTO sales VALUES ('car', 2022, 10);
INSERT INTO sales VALUES ('car', 2021, 9);
INSERT INTO sales VALUES ('car', 2020, 8);
INSERT INTO sales VALUES ('car', 2019, 12);
INSERT INTO sales VALUES ('bike', 2022, 12);
INSERT INTO sales VALUES ('bike', 2021, 10);
INSERT INTO sales VALUES ('bike', 2020, 11);
INSERT INTO sales VALUES ('bike', 2019, 9);

SELECT * FROM sales cur, sales prev
WHERE cur.product=prev.product AND cur.y=prev.y+1;

SELECT cur.product, cur.y, cur.sales, prev.sales FROM sales cur LEFT OUTER JOIN  sales prev ON cur.y=prev.y+1 AND cur.product=prev.product ;

SELECT product, y, sales, lag(sales) OVER 
(PARTITION BY product ORDER BY y )   FROM sales;

CREATE TABLE emp (
id INT AUTO_INCREMENT PRIMARY KEY,
ename CHAR(50) NOT NULL,
department CHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO emp (ename, department, salary) VALUES
('Andy', 'Shipping', 5400),
('Betty', 'Marketing', 6300),
('Tracy', 'Shipping', 4800),
('Mike', 'Marketing', 7100),
('Sandy', 'Sales', 5400),
('James', 'Shipping', 6600),
('Carol', 'Sales', 4600);

SELECT COUNT(*), SUM(salary),
ROUND(AVG(salary), 2) AS AVG
FROM emp;

SELECT department, COUNT(*), SUM(salary),
ROUND(AVG(salary), 2) AS AVG
FROM emp
GROUP BY department
ORDER BY department;

SELECT department, COUNT(*), SUM(salary),
ROUND(AVG(salary), 2) AS AVG
FROM emp
GROUP BY department WITH Rollup
ORDER BY department;

SELECT ename, salary
FROM emp
ORDER BY salary DESC;

SELECT ename, salary, SUM(salary) OVER ()
FROM emp
ORDER BY salary DESC;

SELECT ename, salary,
ROUND(salary / SUM(salary) OVER () * 100, 2) AS pct
FROM emp
ORDER BY salary DESC;

SELECT ename, salary,
SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW)
FROM emp
ORDER BY salary DESC;

SELECT ename, salary,
ROUND(AVG(salary) OVER (), 2) AS AVG
FROM emp
ORDER BY salary DESC;

SELECT ename, salary,
ROUND(AVG(salary) OVER (), 2) AS AVG,
ROUND(salary - AVG(salary) OVER (), 2) AS diff_avg
FROM emp
ORDER BY salary DESC;

SELECT ename, salary,
salary - LEAD(salary, 1) OVER
(ORDER BY salary DESC) AS diff_next
FROM emp
ORDER BY salary DESC;

SELECT ename, salary,
salary - LAST_VALUE(salary) OVER w AS more,
ROUND((salary - LAST_VALUE(salary) OVER w) /
LAST_VALUE(salary) OVER w * 100) AS pct_more
FROM emp
WINDOW w AS (ORDER BY salary DESC ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY salary DESC;

SELECT ename, department, salary,
ROUND(AVG(salary) OVER
(PARTITION BY department), 2) AS AVG,
ROUND(salary - AVG(salary) OVER
(PARTITION BY department), 2) AS diff_avg
FROM emp
ORDER BY department, salary DESC;

SELECT ename, department, salary,
ROUND(AVG(salary) OVER d, 2) AS AVG,
ROUND(salary - AVG(salary) OVER d, 2) AS diff_avg
FROM emp
WINDOW d AS (PARTITION BY department)
ORDER BY department, salary DESC;

SELECT ename, department, salary,
salary - LEAD(salary, 1) OVER
(PARTITION BY department
ORDER BY salary DESC) AS diff_next
FROM emp
ORDER BY department, salary DESC;

SELECT ename, department, salary, RANK() OVER s AS dept_rank,
RANK() OVER (ORDER BY salary DESC) AS global_rank
FROM emp
WINDOW s AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY department, salary DESC;

CREATE TABLE parentof
(parent VARCHAR(10),
child VARCHAR(10));
INSERT INTO parentof VALUES ('Alice', 'Carol');
INSERT INTO parentof VALUES ('Bob', 'Carol');
INSERT INTO parentof VALUES ('Carol', 'Dave');
INSERT INTO parentof VALUES ('Carol', 'George');
INSERT INTO parentof VALUES ('Dave', 'Mary');
INSERT INTO parentof VALUES ('Eve', 'Mary');
INSERT INTO parentof VALUES ('Mary', 'Frank');

WITH RECURSIVE Ancestor AS (
SELECT parent AS p FROM parentof WHERE child='Frank'
UNION ALL
SELECT parent FROM Ancestor a , parentof p
WHERE a.p=p.child)
SELECT * FROM Ancestor

DROP TABLE IF EXISTS parentof;
CREATE TABLE parentof
(parent VARCHAR(10),
child VARCHAR(10),
Birthyear INT);
INSERT INTO parentof VALUES ('Alice', 'Carol',1945);
INSERT INTO parentof VALUES ('Bob', 'Carol',1945);
INSERT INTO parentof VALUES ('Carol', 'Dave',1970);
INSERT INTO parentof VALUES ('Carol', 'George',1972);
INSERT INTO parentof VALUES ('Dave', 'Mary',2000);
INSERT INTO parentof VALUES ('Eve', 'Mary',2000);
INSERT INTO parentof VALUES ('Mary', 'Frank',2020);


WITH RECURSIVE Descendant AS (
SELECT CONCAT(parent ,'->', child) AS lineage, 
child AS c, birthyear, 0 AS ParentAge FROM parentof 
WHERE parent='Alice'
UNION ALL
SELECT CONCAT(parent,'->', child) AS lineage, 
child , p.birthyear, p.birthyear- d.birthyear AS ParentAge
 FROM Descendant d , parentof p
WHERE d.c=p.parent)
SELECT * FROM Descendant;

CREATE TABLE employee(id CHAR, managerid CHAR);

INSERT INTO employee VALUES ('a', NULL);
INSERT INTO employee VALUES ('b', 'a');
INSERT INTO employee VALUES ('c', 'a');
INSERT INTO employee VALUES ('d', 'b');
INSERT INTO employee VALUES ('e', 'c');
INSERT INTO employee VALUES ('f', 'e');


find employee that a manages
WITH RECURSIVE M AS (
        SELECT id FROM Employee WHERE managerid  IN ('a')
        UNION ALL
        SELECT id FROM Employee WHERE managerid  IN (SELECT id FROM M)
               )

SELECT * FROM M;


WITH RECURSIVE M AS (
        SELECT id, managerid   FROM Employee 
        UNION ALL
        SELECT id, managerid   FROM Employee WHERE managerid  IN (SELECT id FROM M)
               )

SELECT * FROM M;


WITH RECURSIVE M AS (
        SELECT id, managerid   FROM Employee 
        UNION ALL
        SELECT E.id, M.managerid   FROM Employee E, M  WHERE E.managerid= M.id
               )

SELECT * FROM M;