/* 
Pulisco il dataset filtrando le colonne che intendo includere 
nella mia analisi in una nuova tabella, 
in modo da non perdere i dati originali
*/
CREATE TABLE FAO_food_loss_waste as
SELECT country, reference_year, loss_percentage, commodity, activity, food_supply_stage, loss_quantity, cause_of_loss, sample_size
FROM food_loss_waste;
--aggiungo la primary key alla nuova tabella
ALTER TABLE FAO_food_loss_waste 
ADD COLUMN id_ serial not null primary key;

--Proseguo la mia pulizia identificando ed eliminando le righe duplicate
DELETE FROM FAO_food_loss_waste
WHERE id_ NOT IN (
	SELECT MIN(id_)
	FROM FAO_food_loss_waste
	GROUP BY country,reference_year,loss_percentage,commodity,activity,food_supply_stage,loss_quantity,cause_of_loss,sample_size);
/*Mi rendo conto che nella lista dei paesi ci sono anche continenti e 
per rendere il dataset più attendibile decido di eliminarli e concentrarmi sui 
singoli stati*/
CREATE TABLE continent as 
SELECT * FROM FAO_food_loss_waste
WHERE country LIKE '%Asia'
OR country LIKE '%Europe'
OR country = 'Africa';

/*
ora che ho raggruppato le righe che mi interessava eliminare
in un'altra tabella in modo da poterle, se voglio, analizzarle in seguito;
le elimino da FAO_food_loss_waste
*/
DELETE FROM FAO_food_loss_waste
WHERE country LIKE '%Asia'
OR country LIKE '%Europe'
OR country = 'Africa';

--fine pulizia dei dati

--Paesi più virtuosi:
SELECT country, sum(cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric)) 
AS total_losswaste
FROM fao_food_loss_waste
GROUP BY country
ORDER BY total_losswaste ASC NULLS LAST
LIMIT 30;
--Paesi meno virtuosi:
SELECT country, sum(cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric))
AS total_losswaste
FROM fao_food_loss_waste
GROUP BY country
ORDER BY total_losswaste DESC NULLS LAST
LIMIT 30;

--Tipologie di cibo maggiormente sprecate negli ultimi 20 anni:
SELECT commodity, sum(cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric))
AS total_losswaste
FROM fao_food_loss_waste
GROUP BY commodity
ORDER BY total_losswaste DESC NULLS LAST
LIMIT 30;

--Qual'è il rapporto tra la fase di approvvigionamento e la quantità percentuale di cibo sprecata?
SELECT DISTINCT(food_supply_stage),avg(loss_percentage)
AS total
FROM fao_food_loss_waste
GROUP BY food_supply_stage 
ORDER BY avg(loss_percentage) DESC NULLS LAST
LIMIT 50;

--Quali sono le 10 maggiori cause di spreco/perdita?
SELECT DISTINCT(cause_of_loss), sum((cast(SUBSTRING(loss_quantity, '(\d+(\.\d+)?)') as numeric)))
AS causes_total
FROM fao_food_loss_waste
GROUP BY DISTINCT(cause_of_loss)
ORDER BY causes_total DESC NULLS LAST
LIMIT 10;

--C'è stato un miglioramento nella quantità di cibo sprecato tra l'inizio e la fine dell'intervallo analizzato?
SELECT round(avg(CASE WHEN reference_year>='2016' AND reference_year <='2021' THEN loss_percentage END)) AS avg_now,
round(avg(CASE WHEN reference_year>='2000' AND reference_year <='2005' THEN loss_percentage END)) AS avg_pre
from fao_food_loss_waste;

-- Quali paesi sono peggiorati in termini di spreco alimentare negli ultimi 5 anni?
SELECT DISTINCT(COUNTRY), SUM(loss_percentage) FROM fao_food_loss_waste
WHERE reference_year>'2016'
GROUP BY country
HAVING SUM(loss_percentage)>0
ORDER BY SUM(loss_percentage) DESC NULLS LAST;

--Fine query