queries_example.sql

-- Query SQL di esempio per l'analisi dei dati

-- 1. Conteggio consumazioni per presidio
-- Utile per analizzare la distribuzione geografica delle mense
SELECT 
    presidio,
    COUNT(*) as numero_consumazioni,
    ROUND(AVG(prezzo_totale), 2) as prezzo_medio,
    SUM(prezzo_totale) as fatturato_totale
FROM consumazioni_cleaned
GROUP BY presidio
ORDER BY numero_consumazioni DESC;

-- 2. Analisi temporale per mese
-- Mostra l'andamento delle consumazioni nei diversi mesi
SELECT 
    mese_rif,
    COUNT(*) as consumazioni,
    COUNT(DISTINCT utente_cognome) as utenti_unici,
    ROUND(AVG(prezzo_totale), 2) as prezzo_medio
FROM consumazioni_cleaned
GROUP BY mese_rif
ORDER BY mese_rif;

-- 3. Top 10 tipi di pasto più venduti
-- Identifica le preferenze degli utenti
SELECT 
    tipo_pasto,
    COUNT(*) as numero_acquisti,
    ROUND(AVG(prezzo_totale), 2) as prezzo_medio
FROM consumazioni_cleaned
GROUP BY tipo_pasto
ORDER BY numero_acquisti DESC
LIMIT 10;

-- 4. Analisi utenti per numero di consumazioni
-- Trova gli utenti più frequenti
SELECT 
    utente_cognome,
    COUNT(*) as num_consumazioni,
    ROUND(SUM(prezzo_totale), 2) as spesa_totale,
    ROUND(AVG(prezzo_totale), 2) as spesa_media
FROM consumazioni_cleaned
GROUP BY utente_cognome
HAVING COUNT(*) > 5
ORDER BY num_consumazioni DESC
LIMIT 20;

-- 5. Distribuzione prezzi
-- Raggruppa le consumazioni per fascia di prezzo
SELECT 
    CASE 
        WHEN prezzo_totale = 0 THEN 'Gratuito'
        WHEN prezzo_totale > 0 AND prezzo_totale <= 5 THEN '0-5 euro'
        WHEN prezzo_totale > 5 AND prezzo_totale <= 10 THEN '5-10 euro'
        ELSE 'Oltre 10 euro'
    END as fascia_prezzo,
    COUNT(*) as numero_consumazioni
FROM consumazioni_cleaned
GROUP BY fascia_prezzo
ORDER BY 
    CASE fascia_prezzo
        WHEN 'Gratuito' THEN 1
        WHEN '0-5 euro' THEN 2
        WHEN '5-10 euro' THEN 3
        ELSE 4
    END;

-- 6. Analisi giornaliera (giorno della settimana più frequentato)
-- Richiede l'aggiunta del campo giorno_settimana nella pipeline
-- SELECT 
--     strftime('%w', data_consumazione) as giorno_settimana,
--     COUNT(*) as consumazioni
-- FROM consumazioni_cleaned
-- GROUP BY giorno_settimana
-- ORDER BY giorno_settimana;

-- 7. Pivot presidio x mese
-- Mostra la distribuzione mensile per ciascun presidio
SELECT 
    presidio,
    SUM(CASE WHEN mese_rif = 1 THEN 1 ELSE 0 END) as gennaio,
    SUM(CASE WHEN mese_rif = 2 THEN 1 ELSE 0 END) as febbraio,
    SUM(CASE WHEN mese_rif = 3 THEN 1 ELSE 0 END) as marzo,
    COUNT(*) as totale
FROM consumazioni_cleaned
GROUP BY presidio
ORDER BY totale DESC;