/* 
Each query was formatted using sqlfluff : sqlfluff fix -e L011,L014,L031,L051 --dialect postgres requete.sql  
*/

--Q2.1
/* When using psql:

\d

\d categories;
\d customers;
...

When using psql, these psql-specific instructions are generally more useful,
but we could also query the system tables where postgresql stores metadata about the database :

select tablename
from pg_tables
where schemaname = 'public';
*/

-- Q2.2
-- fact table: order_details
-- recursive dimension: employees



-- Exercise GROUP BY extensions
-- customer.city and order.ship_city seem to refer to the same property.
-- I am assuming that customer country/city, etc is the shipping_country, etc...
-- in this DB instance it is true BUT it may differ actually (if sending product to a friend, offering a present etc.)
-- BEWARE: unit_price in OD and P differ!

-- SELECT OD.Order_ID, OD.Quantity,P.product_name,OD.Unit_Price,OD.discount,P.Unit_Price
-- FROM Order_details OD, Products P
-- WHERE OD.product_id=P.product_id AND OD.Unit_Price <> P.Unit_Price

-- SELECT C.city, O.ship_city FROM orders O
-- INNER JOIN customers C ON O.CustomerID=C.CustomerID
-- WHERE C.city <> O.Ship_city
-- GROUP BY C.city,O.ship_city;


-- Q2.3.1
SELECT
    country,
    count(*) AS nb
FROM customers
GROUP BY country;

-- Q2.3.2
SELECT
    ship_country,
    ship_city,
    count(*) AS nbOrders
FROM Orders
GROUP BY rollup(ship_country, ship_city)
ORDER BY ship_country, ship_city

-- Q2.3.3
/* Note the "distinct" here. 
According to the keys (FK), the table obtained after joining input tables (and before grouping lines) contains one line  the resulting table
per line of order detail. */
SELECT
    C.country AS C_Country,
    S.country AS S_Country,
    SUM(OD.Quantity) AS Quantity,
    COUNT(DISTINCT OD.Order_ID) AS NBOrder
FROM
    Orders O
JOIN Customers C ON O.customer_id = C.customer_ID
JOIN Order_Details OD ON O.Order_id = OD.Order_ID
JOIN Products P ON OD.Product_ID = P.Product_ID
JOIN Suppliers S ON P.Supplier_ID = S.Supplier_ID
GROUP BY C.country, S.country
ORDER BY C.country, S.country;

-- Q2.3.4
SELECT
    C.country AS C_Country,
    S.country AS S_Country,
    SUM(Quantity) AS Quantity,
    COUNT(DISTINCT OD.Order_ID) AS NBOrder
FROM
    Orders O
JOIN Customers C ON O.customer_id = C.customer_ID
JOIN Order_Details OD ON O.Order_id = OD.Order_ID
JOIN Products P ON OD.Product_ID = P.Product_ID
JOIN Suppliers S ON P.Supplier_ID = S.Supplier_ID
GROUP BY CUBE(C.country, S.country)
ORDER BY C.country, S.country;

-- Q2.3.5
/* The (unit_)price attribute must be selected from Order_details.
Leave out of the ROLLUP the attributes that appear in all of the groupings. */

SELECT
    ship_country,
    ship_region,
    ship_city,
    SUM(OD.Quantity * OD.Unit_price * (1 - OD.Discount)) AS price
FROM
    Orders O
JOIN Customers C ON O.customer_id = C.customer_ID
JOIN Order_Details OD ON O.Order_id = OD.Order_ID
JOIN Products P ON OD.Product_ID = P.Product_ID
JOIN Suppliers S ON P.Supplier_ID = S.Supplier_ID
WHERE S.Country = 'France'
GROUP BY ship_country, ROLLUP(ship_region, ship_city);

/*
Alternative solution: replace the last line of the query with :
GROUP BY GROUPING SETS((ship_country, ship_region,ship_city),(ship_country,ship_region),(ship_country))
*/

-- Q2.3.6
/* The latest versions versions of postgresql support GROUPING but not the older versions.
We must use a CASE here and not DECODE (DECODE is specific to Oracle)
*/



SELECT
    ship_country AS S_country,
    CASE WHEN GROUPING(ship_country) = 0 AND GROUPING(ship_city) = 1
        THEN 'whole country'
        ELSE ship_city END AS S_city,
    COUNT(*) AS nbOrders
FROM Orders
GROUP BY ROLLUP(ship_country, ship_city)
ORDER BY ship_country, ship_city;

-- Exercise Windowing

-- Q2.4.1.
/* 
- We want to group the lines differently on column nbc_b and nbc_p, and order_id ... (no grouping there),
therefore we should use windows, defined with OVER().
- There is no reason to group the lines first with a GROUP BY since we want to keep every line of the input table
to display its order_id, order_date, etc.
*/
SELECT
    order_id,
    order_date,
    ship_city,
    ship_country,
    freight,
    count(*) OVER (PARTITION BY ship_city) AS nbc_v,
    count(*) OVER (PARTITION BY ship_country) AS nbc_p,
    sum(freight) OVER (PARTITION BY ship_country) AS poids_p,
    sum(freight) OVER (PARTITION BY ship_country, order_date) AS poids_p_j
FROM Orders;


-- Q2.4.2.
SELECT
    order_id,
    order_date,
    ship_city,
    ship_country,
    count(*) OVER (PARTITION BY ship_city ORDER BY order_date) AS nc_v,
    count(*) OVER (PARTITION BY ship_country ORDER BY order_date) AS nc_p,
    count(*) OVER (ORDER BY order_date) AS nc,
    count(*) OVER (PARTITION BY ship_country, order_date) AS n_pj
FROM Orders;

-- Q2.4.3.
SELECT
    order_id,
    order_date,
    ship_city,
    ship_country,
    count(*) OVER (PARTITION BY ship_city ORDER BY order_date) AS nc_v,
    count(*) OVER (PARTITION BY ship_country ORDER BY order_date) AS nc_p,
    count(*) OVER (ORDER BY order_date) AS nc,
    count(*) OVER (PARTITION BY ship_country, order_date) AS n_pj,
    count(*) OVER (PARTITION BY ship_country ORDER BY order_date)
    - count(*) OVER (PARTITION BY ship_country, order_date) AS nc_p2
FROM Orders;

-- Q2.4.4.
SELECT
    order_id,
    LAG(order_id) OVER (ORDER BY order_date) AS num_p,
    LAG(order_id) OVER (PARTITION BY ship_city ORDER BY order_date) AS num_p_v
FROM Orders;



-- Q2.4.5
/* Here it makes sense and is best to first group the data per city, since we can compute 
all the desired values (including agregates) from that data summarized at the city level.
Why add the country in addition to the city in the grouping attributes ?
- We want to use the country in each group (it must be displayed)
- We have not defined a city -> country functional dependency (city is not a PK). 
Actually, the functional dependency would not hold here because some cities might have homonyms in other countries. 
- Even if the functional dependency did hold and had been specified somehow, it's best to add country to the group 
and not rely on the functional dependency to let the DBMS infer the country from the city 
(this is not supported by some DBMS sinci it is an optional part of the SQL standard).
*/

SELECT
    ship_country,
    ship_city,
    count(*) AS nbOrders,
    sum(count(*)) OVER (PARTITION BY ship_country) AS nbOrdCty,
    max(count(*)) OVER (PARTITION BY ship_country) AS nbOrMaxCty
FROM Orders
GROUP BY ship_country, ship_city;


-- Q2.4.6
SELECT
    ship_country,
    ship_city,
    count(*) AS nbOrders,
    dense_rank() OVER (PARTITION BY ship_country ORDER BY count(*)) AS Rank
FROM Orders
GROUP BY ship_country, ship_city;


-- Q2.4.7
/* In Oracle we couls use RATIO_TO_REPORT instead of dividing the two expressions. */

SELECT
    ship_country,
    ship_city,
    count(*) AS nbOrders,
    dense_rank() OVER (PARTITION BY ship_country ORDER BY count(*)) AS Rank,
    1.0 * count(*) / (sum(count(*)) OVER(PARTITION BY ship_country)) AS PERCENTG
FROM Orders
GROUP BY ship_country, ship_city;


-- Q2.4.8
WITH temp AS (SELECT
    order_id,
    SUM(Quantity * Unit_price * (1 - Discount)) AS price,
    LAG(
        SUM(Quantity * Unit_price * (1 - Discount))
    ) OVER(ORDER BY order_id) AS priceprev
    FROM Order_Details
    GROUP BY order_id
)
SELECT
    order_id,
    price
FROM temp
WHERE price < 1.1 * priceprev;



-- Q2.4.9
/* the most clever solution, probably the best: using FIRST_VALUE.
In postgres, we could define the window w to avoid repetition... and that would probably be even better, 
but the solution below will be valid on oracle too : */
SELECT DISTINCT
    EXTRACT(YEAR FROM Order_date) AS year,
    FIRST_VALUE(
        product_name
    ) OVER(
        PARTITION BY EXTRACT(YEAR FROM Order_date) ORDER BY SUM(quantity) DESC
    ) AS product_name,
    FIRST_VALUE(
        SUM(quantity)
    ) OVER(
        PARTITION BY EXTRACT(YEAR FROM Order_date) ORDER BY SUM(quantity) DESC
    ) AS qtity
FROM Orders O
JOIN Order_Details OD ON O.Order_id = OD.Order_ID
JOIN Products P ON OD.Product_ID = P.Product_ID
GROUP BY EXTRACT(YEAR FROM Order_date), OD.product_id, product_name;



/* alternative solution : */
WITH temp AS (
    SELECT
        product_name,
        EXTRACT(YEAR FROM Order_date) AS year,
        SUM(Quantity) AS qtity
    FROM Orders O
    JOIN Order_Details OD ON O.Order_id = OD.Order_ID
    JOIN Products P ON OD.Product_ID = P.Product_ID
    GROUP BY EXTRACT(YEAR FROM Order_date), OD.product_id, product_name
),
t2 AS (
    SELECT
        year,
        MAX(qtity) AS qtity
    FROM temp GROUP BY year
)
SELECT
    temp.year,
    temp.product_name,
    temp.qtity
FROM temp
JOIN t2 ON temp.year = t2.year AND temp.qtity = t2.qtity;

/* another alternative solution : */
WITH temp AS (
    SELECT
        product_name,
        EXTRACT(YEAR FROM Order_date) AS year,
        SUM(Quantity) AS qtity,
        MAX(
            SUM(Quantity)
        ) OVER (PARTITION BY EXTRACT(YEAR FROM Order_date)) AS mxqt
    FROM Orders O
    JOIN Order_Details OD ON O.Order_id = OD.Order_ID
    JOIN Products P ON OD.Product_ID = P.Product_ID
    GROUP BY EXTRACT(YEAR FROM Order_date), OD.product_id, product_name
)
SELECT
    year,
    product_name,
    qtity
FROM temp
WHERE qtity = mxqt;




-- Exercise Hierarchies
-- Q2.5

/* Oracle offers an alternative, somewhat more complex and non-standard 
way of computing such recursive queries based on CONNECT BY instead of CTEs:
SELECT LEVEL mylis FROM DUAL CONNECT BY LEVEL<60;
The CTE approach to implement recursion is better because it's standard and therefore can be used in postgres,
and it is more expressive.
*/
WITH RECURSIVE liste_entiers(n) as
(
SELECT 1 AS n
UNION ALL
SELECT n+1 FROM liste_entiers
WHERE n<=59
)
SELECT n from liste_entiers;

-- Q2.6
WITH RECURSIVE e(eid, niv, chemin, nomindent) AS (
    SELECT
        employee_id,
        0,
        CAST(lastname AS TEXT),
        CAST(lastname AS TEXT)
    FROM employees
    WHERE reports_to IS NULL
    UNION ALL
    SELECT
        employee_id,
        niv + 1,
        lastname || '/' || chemin,
        REPEAT(' ', 2 * (niv + 1)) || CAST(lastname AS TEXT)
    FROM employees JOIN e ON reports_to = eid
)
SELECT
    nomindent,
    niv,
    chemin
FROM e
ORDER BY chemin;

-- Q4.1

SELECT order_id, order_date, freight,
       RANK() OVER (ORDER BY freight DESC) AS rank
FROM orders
ORDER BY freight DESC, order_id;

-- Q4.2

SELECT order_id, order_date, freight,
       DENSE_RANK() OVER (ORDER BY freight DESC) AS rank
FROM orders
ORDER BY freight DESC, order_id;

-- Q4.3

SELECT order_id, order_date, freight,
       ROW_NUMBER() OVER (ORDER BY freight DESC) AS row_number
FROM orders
ORDER BY freight DESC, order_id;

-- Q4.4

SELECT order_id, order_date, freight,
       NTILE(120) OVER (ORDER BY freight DESC) AS tile
FROM orders
ORDER BY freight DESC, order_id;

-- Q4.5

SELECT order_id, order_date, freight,
       LEAD(freight, 2) OVER (ORDER BY order_date) AS next_freight
FROM orders
ORDER BY order_date;

-- Q4.6

SELECT order_id, order_date, freight,
       COALESCE(LAG(freight) OVER (ORDER BY order_date), 0) AS previous_freight
FROM orders
ORDER BY order_date;

-- Q4.7

SELECT order_id, order_date, freight,
       FIRST_VALUE(freight) OVER (ORDER BY order_date) AS first_freight
FROM orders
ORDER BY order_date;

-- Q4.8

SELECT order_id, order_date, freight,
       LAST_VALUE(freight) OVER (
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_freight
FROM orders
ORDER BY order_date;