FoodEx · Food
Delivery Data Warehouse

-- What % of registered customers never placed a single order?
SELECT
    ROUND(
        COUNT(CASE WHEN c.cust_id NOT IN (
            SELECT o.`cust_id(FK)` FROM `Order` o
        ) THEN 1 END) * 100.0
        / COUNT(c.cust_id),
    2) AS bounce_rate_pct
FROM Customer c
WHERE c.cust_user = 1;  -- Active users only

-- Delivery exec performance score (1–5) based on delay vs. expected ETA
SELECT
    de.name,
    ROUND(AVG(MINUTE(TIMEDIFF(
        o.order_delivered_time, o.expected_delivery_time
    ))), 1) AS avg_delay_mins,
    CASE
        WHEN AVG(MINUTE(TIMEDIFF(o.order_delivered_time, o.expected_delivery_time))) <= 0   THEN 5
        WHEN AVG(MINUTE(TIMEDIFF(o.order_delivered_time, o.expected_delivery_time))) <= 10  THEN 4
        WHEN AVG(MINUTE(TIMEDIFF(o.order_delivered_time, o.expected_delivery_time))) <= 20  THEN 3
        ELSE 1
    END AS performance_score
FROM `Order` o
JOIN Delivery_Exec de ON o.`delivery_exec_id(FK)` = de.delivery_exec_id
JOIN Feedback f ON o.order_id = f.`order_id (FK)` AND f.rated_person = 'D'
GROUP BY de.delivery_exec_id, de.name
ORDER BY avg_delay_mins ASC;