-- Query for Dashboard 1
-- Extracting data related to orders, items, and addresses
SELECT
    o.order_id,
    i.item_price,
    o.quantity,
    i.item_cat,
    i.item_name,
    o.created_at,
    a.delivery_address1,
    a.delivery_address2,
    a.delivery_city,
    a.delivery_zipcode,
    o.delivery
FROM 
    BensPizzeria..orders o
LEFT JOIN item i
    ON o.item_id = i.item_id
LEFT JOIN address a
    ON o.add_id = a.add_id

-- Query for Dashboard 2
-- Extracting ingredient-level insights and costs
SELECT
    s2.ing_name,
    s2.ordered_weight,
    CAST(ing.ing_weight AS BIGINT) * CAST(inv.quantity AS BIGINT) AS total_inv_weight,
    (CAST(ing.ing_weight AS BIGINT) * CAST(inv.quantity AS BIGINT)) - s2.ordered_weight AS remaining_weight
FROM (
    SELECT 
        ing_id,
        ing_name,
        SUM(CAST(ordered_weight AS BIGINT)) AS ordered_weight
    FROM stock1
    GROUP BY
        ing_name,
        ing_id
) s2
LEFT JOIN inventory inv
    ON inv.item_id = s2.ing_id
LEFT JOIN ingredient ing
    ON ing.ing_id = s2.ing_id

-- Query for Dashboard 3
-- Extracting staffing and cost insights
SELECT
    r.date,
    s.first_name,
    s.last_name,
    s.hourly_rate,
    sh.start_time,
    sh.end_time,
    (DATEDIFF(hour, sh.start_time, sh.end_time) + DATEDIFF(minute, sh.start_time, sh.end_time)) / 60 AS hours_in_shift,
    ((DATEDIFF(hour, sh.start_time, sh.end_time) + DATEDIFF(minute, sh.start_time, sh.end_time)) / 60) * s.hourly_rate AS staff_cost
FROM rota r
LEFT JOIN staff s
    ON r.staff_id = s.staff_id
LEFT JOIN shift sh
    ON r.shift_id = sh.shift_id

Google Looker Dashboard: A Visual Journey