%reload_ext sql

!psql -h localhost -c 'DROP DATABASE IF EXISTS lec13'
!psql -h localhost -c 'CREATE DATABASE lec13'

DROP DATABASE
CREATE DATABASE

%sql postgresql://127.0.0.1:5432/lec13

%%sql
DROP TABLE IF EXISTS empsalary;
CREATE TABLE empsalary (
    depname TEXT,
    empno INTEGER,
    salary INTEGER
);

INSERT INTO empsalary VALUES
    ('develop',  11, 5200),
    ('develop',   7, 4200),
    ('develop',   9, 4500),
    ('develop',   8, 6000),
    ('develop',  10, 5200),
    ('personnel', 5, 3500),
    ('personnel', 2, 3900),
    ('sales',     3, 4800),
    ('sales',     1, 5000),
    ('sales',     4, 4800)
;

%%sql
SELECT depname, empno, salary,
       avg(salary) OVER (PARTITION BY depname)
FROM empsalary;

%%sql
SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

%%sql
SELECT depname, salary, sum(salary) OVER () FROM empsalary;

%%sql
SELECT depname, salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

%%sql
SELECT depname, salary,
   sum(salary) OVER (
       ORDER BY salary
       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM empsalary;

%%sql
SELECT depname, salary,
       sum(salary) OVER (
           ORDER BY salary
           RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM empsalary;

%%sql
SELECT depname, salary,
       sum(salary) OVER (
           PARTITION BY depname
           ORDER BY salary)
FROM empsalary;

%%sql
SELECT
    depname, salary,
    sum(salary) OVER () AS a,
    sum(salary) OVER (ORDER BY salary) AS b,
/*    sum(salary) OVER (
      ORDER BY salary
      RANGE BETWEEN UNBOUNDED 
        PRECEDING AND CURRENT ROW) AS b, */
    sum(salary) OVER (PARTITION BY 
      depname ORDER BY salary) AS c
FROM empsalary;

%%sql
WITH RECURSIVE t(n) AS (
    VALUES (10)
  UNION ALL
    SELECT n-1 FROM t WHERE n > 0 
)
SELECT n FROM t;

%%sql
/* A bit harder to understand */
WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 10
)
SELECT n FROM t;

%%sql
DROP TABLE IF EXISTS parts;
CREATE TABLE parts (
    part TEXT, sub_part TEXT, quantity INTEGER
);

INSERT INTO parts VALUES
    ('A', 'A.1', 1),
    ('A', 'A.2', 2),
    ('A', 'A.3', 3),
    ('B', 'B.1', 1),
    ('B', 'B.2', 2),
    ('B.2', 'B.2.i', 10),
    ('C', 'C.1', 1),
    ('C.1', 'C.1.i', 10),
    ('C.1.i', 'C.1.i.a', 10),
    ('C.1.i', 'C.1.i.b', 20),
    ('C.1.i.a', 'C.1.i.a.1', 30)
;

%%sql 
SELECT * FROM parts;

%%sql
WITH RECURSIVE included_parts(sub_part, part, quantity, depth) AS (
    SELECT sub_part, part, quantity, 0 FROM parts WHERE part = 'C'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity * pr.quantity, pr.depth + 1
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, depth, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part, depth
ORDER BY depth;

%config SqlMagic.displaylimit=30

%%sql
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    region TEXT,
    product TEXT,
    quantity INTEGER,
    amount FLOAT
);

INSERT INTO orders VALUES
    ('US', 'apple', 100, 175.40),
    ('US', 'apple', 80, 99.70),
    ('US', 'banana', 50, 80.60),
    ('US', 'cheerios', 1000, 2500.95),
    ('Canada', 'apple', 500, 198.00),
    ('Canada', 'banana', 20, 45.50),
    ('Mexico', 'apple', 1000, 1100.39),
    ('Mexico', 'cheerios', 200, 500.50),
    ('Mexico', 'cheerios', 400, 430.65),
    ('Australia', 'banana', 40, 80.00),
    ('Australia', 'banana', 50, 99.45),
    ('Australia', 'banana', 2000, 2100.60),
    ('Australia', 'banana', 60, 75.00),
    ('Australia', 'cheerios', 500, 760.94)
;

SELECT * FROM orders;

%%sql
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

depname	salary	a	b	c
personnel	3500	47100	3500	3500
personnel	3900	47100	7400	7400
develop	4200	47100	11600	4200
develop	4500	47100	16100	8700
sales	4800	47100	25700	9600
sales	4800	47100	25700	9600
sales	5000	47100	30700	14600
develop	5200	47100	41100	19100
develop	5200	47100	41100	19100
develop	6000	47100	47100	25100

Lecture 13¶

Window Functions¶

Window frames¶

For slide display purposes¶

Recursive Queries¶

CTEs¶

depname	empno	salary	avg
develop	11	5200	5020.0000000000000000
develop	7	4200	5020.0000000000000000
develop	9	4500	5020.0000000000000000
develop	8	6000	5020.0000000000000000
develop	10	5200	5020.0000000000000000
personnel	5	3500	3700.0000000000000000
personnel	2	3900	3700.0000000000000000
sales	3	4800	4866.6666666666666667
sales	1	5000	4866.6666666666666667
sales	4	4800	4866.6666666666666667

depname	salary	sum
personnel	3500	None
personnel	3900	3500
develop	4200	7400
develop	4500	11600
sales	4800	16100
sales	4800	16100
sales	5000	25700
develop	5200	30700
develop	5200	30700
develop	6000	41100

part	sub_part	quantity
A	A.1	1
A	A.2	2
A	A.3	3
B	B.1	1
B	B.2	2
B.2	B.2.i	10
C	C.1	1
C.1	C.1.i	10
C.1.i	C.1.i.a	10
C.1.i	C.1.i.b	20

region	product	quantity	amount
US	apple	100	175.4
US	apple	80	99.7
US	banana	50	80.6
US	cheerios	1000	2500.95
Canada	apple	500	198.0
Canada	banana	20	45.5
Mexico	apple	1000	1100.39
Mexico	cheerios	200	500.5
Mexico	cheerios	400	430.65
Australia	banana	40	80.0
Australia	banana	50	99.45
Australia	banana	2000	2100.6
Australia	banana	60	75.0
Australia	cheerios	500	760.94