kk

-- A customer is called a commuter if she buys at least 70% of her purchases in stores outside her city.
-- Give an SQL query to find the number of commuter customers by customer city.


WITH temp AS (SELECT S.store_id, C.city, C.customer_id, SUM(store_sales) AS Tot,
                SUM(CASE WHEN C.city <> store_city THEN store_sales ELSE 0 END) AS TotFuoriCitta
                FROM store S, sales_fact AS SL, customer AS C
                WHERE S.store_id = SL.store_id AND SL.customer_id = C.customer_id
                GROUP BY S.store_id,C.city, C.customer_id)


SELECT *, SUM(CASE WHEN 100*TotFuoriCitta/Tot >=70 THEN 1 ELSE 0 END) AS NCust
FROM temp
GROUP BY city