DW-TP1

-- Question 1
-- The schema used will be the star schema

-- Question 2
--2.1
SELECT a.ville, a.age, l.categorie , ex.etat, COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY(a.ville, a.age, l.categorie, ex.etat);

-- We can see that it's correct because the resulting table contains the same number of emprunts that the table emprunts (25)
SELECT COUNT(*)
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero;

-- 2.2
-- The rollup function calculate subtotals so, for example, how many books in bon etat,
-- how many romans in any etat, how many books empruntes by an age no mattter the genre or etat
-- the categories that are not important for the subtotal are represented by null

SELECT a.ville, a.age, l.categorie , ex.etat,COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY a.ville, rollup(a.age, l.categorie, ex.etat);

SELECT a.age, a.ville, l.categorie , ex.etat,COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY a.age,a.ville , rollup( l.categorie, ex.etat);

SELECT a.ville, a.age, l.categorie , ex.etat,COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY rollup (a.ville, a.age, l.categorie, ex.etat);


-- 2.3
-- Faz todas as permutaçoes possiveis
SELECT a.ville, a.age, l.categorie , ex.etat, COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY a.ville, cube(a.age, l.categorie, ex.etat);

SELECT a.ville, a.age, l.categorie , ex.etat, COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY cube (a.ville, a.age, l.categorie, ex.etat);


-- Os dois extremos: GROUP BY - te diz algo sobre exatamente um cuboide
--                   GROUP BUY CUBE - Calcula TUDO, todas as permutaçoes possiveis com as colunas e dados dado
--                                    o que acaba sendo muita coisa e quase impossivel de calcular

-- 2.4
-- You have all the granular info and also the subtotals, for each of the parameters specified
SELECT a.ville, a.age, l.categorie , ex.etat, COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY cube (a.ville, a.age, l.categorie, ex.etat), GROUPING sets(a.ville, a.age);

-- 2.5
SELECT COALESCE(a.ville, 'All cities'), COALESCE(to_char(a.age), 'All ages'), COALESCE(l.categorie, 'All categories') AS cat , COALESCE(ex.etat, 'All states'), COUNT(*) AS nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY cube (a.ville, a.age, l.categorie, ex.etat);


-- Question 1 (3)
-- The cube's density is height*width*depth?

-- Question 2 (4)
-- 2.1
SELECT COALESCE(a.ville, 'All cities'), COALESCE(to_char(a.age), 'All ages'), COALESCE(l.categorie, 'All categories') AS cat ,
COALESCE(ex.etat, 'All states'), COUNT(*) AS nb_emprunt,
dense_rank() OVER (ORDER BY  COUNT(*)) AS rank
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY cube (a.ville, a.age, l.categorie, ex.etat);

--2.2
SELECT a.num_ab, a.ville, COUNT(a.num_ab) AS total_abonne,
(SELECT COUNT(a1.ville) FROM emprunt e1 JOIN abonne a1 ON e1.num_ab = a1.num_ab WHERE a1.ville = a.ville) AS total_ville,
ratio_to_report(COUNT(a.num_ab)) OVER (partition BY a.ville) AS ratio
FROM emprunt e JOIN abonne a ON e.num_ab = a.num_ab
GROUP BY (a.num_ab,a.ville);

--2.3
SELECT ex.etat, ratio_to_report(COUNT(*)) OVER () AS ratio, COUNT(*) AS emprunts,
SUM(COUNT(*)) OVER (ORDER BY etat ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY (ex.etat);

--2.4
SELECT l.categorie, a.ville, a.age,  ex.etat,
( SELECT MAX(COUNT(*))
  FROM (emprunt e1 NATURAL JOIN abonne a1), (exemplaire ex1 NATURAL JOIN livre l1)
  WHERE e1.num_ex = ex1.numero AND l1.categorie = l.categorie
  GROUP BY(a1.ville, a1.age, l1.categorie, ex1.etat)
) AS max_nb_emprunt
FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
WHERE e.num_ex = ex.numero
GROUP BY(a.ville, a.age, l.categorie, ex.etat)
HAVING COUNT(*) =
  (SELECT MAX(COUNT(*))
  FROM (emprunt e1 NATURAL JOIN abonne a1), (exemplaire ex1 NATURAL JOIN livre l1)
  WHERE e1.num_ex = ex1.numero AND l1.categorie = l.categorie
  GROUP BY(a1.ville, a1.age, l1.categorie, ex1.etat))
ORDER BY (l.categorie);

-- Question 3 (5)

--3.1
SELECT COALESCE(ville, 'All cities'), COALESCE(to_char(age), 'All ages'), COALESCE(categorie, 'All categories') AS cat ,
COALESCE(etat, 'All states'), nb_emprunt
FROM (
  SELECT  ville ,age , categorie, etat,COUNT(*) AS nb_emprunt, dense_rank() OVER (ORDER BY  COUNT(*) DESC) AS r
  FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
  WHERE e.num_ex = ex.numero
  GROUP BY cube (a.ville, a.age, l.categorie, ex.etat))
WHERE r <=2;

--3.2
SELECT COALESCE(ville, 'All cities'), COALESCE(to_char(age), 'All ages'), COALESCE(categorie, 'All categories') AS cat ,
COALESCE(etat, 'All states'), nb_emprunt
FROM (
  SELECT  ville ,age , categorie, etat,COUNT(*) AS nb_emprunt, dense_rank() OVER (ORDER BY  COUNT(*)) AS r
  FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
  WHERE e.num_ex = ex.numero
  GROUP BY cube (a.ville, a.age, l.categorie, ex.etat))
WHERE r <=2;


-- 3.3

SELECT  categorie,ville ,age, etat,COUNT(*) AS nb_emprunt, dense_rank() OVER (partition BY l.categorie ORDER BY COUNT(*), categorie) AS r
  FROM (emprunt e NATURAL JOIN abonne a), (exemplaire ex NATURAL JOIN livre l)
  WHERE e.num_ex = ex.numero
  GROUP BY cube (a.ville, a.age, l.categorie, ex.etat);