Inventory & Margin Optimization

SELECT 
    DAYNAME(order_date) AS day_of_week, 
    HOUR(order_time) AS hour_of_day, 
    COUNT(DISTINCT order_id) AS number_of_orders 
FROM fact_orders 
GROUP BY 
    DAYOFWEEK(order_date), day_of_week, hour_of_day 
ORDER BY 
    DAYOFWEEK(order_date), number_of_orders DESC;

WITH total_order_revenue AS (
    SELECT 
        m.item_name, 
        COUNT(o.order_id) AS total_orders, 
        SUM(m.price) AS total_revenue 
    FROM dim_menu_items m 
    INNER JOIN fact_orders o ON m.menu_item_id = o.item_id 
    GROUP BY m.item_name
), 
ItemRanks AS (
    SELECT 
        item_name, total_orders, total_revenue, 
        NTILE(2) OVER (ORDER BY total_orders DESC) AS order_ntile, 
        NTILE(2) OVER (ORDER BY total_revenue DESC) AS revenue_ntile 
    FROM total_order_revenue
) 
SELECT 
    item_name, total_orders, total_revenue, 
    CASE 
        WHEN order_ntile = 1 AND revenue_ntile = 1 THEN 'Star'
        WHEN order_ntile = 1 AND revenue_ntile = 2 THEN 'Workhorse'
        WHEN order_ntile = 2 AND revenue_ntile = 1 THEN 'Puzzle'
        WHEN order_ntile = 2 AND revenue_ntile = 2 THEN 'Dud' 
    END AS item_category 
FROM ItemRanks;