Ben's Pizzeria Project: Using SQL and Looker Studio

Hello! 

For this project, I began by setting up/connecting to my local MySQL Workbench server. I then used QuickDB to formulate the schema necessary for my data. I created a database, executed the schema, and then filled it with its corresponding data from a fictional dataset and company, "Ben's Pizza".

 I then wrote custom SQL queries and 'views', corresponding with each client's brief demand. To visualize the results, I connected to the pizza MySQL database on Looker Studio by creating a Google Cloud "instance" that allowed me to connect to my data, given a code, database name, username, and password. Finally, I was able to create three interactive dashboards, each with essential KPI's that form to help tell the data's story and promote data-driven decision-making for any potential shareholders.

The following SQL query is for "Orders Data Required":

-- Selecting the required columns from the tables

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

    orders AS o

    LEFT JOIN item AS i ON o.item_id = i.item_id -- Performing a left join between the orders and item tables using the item_id column

    LEFT JOIN address AS a ON o.add_id = a.add_id; -- Performing a left join between the orders and address tables using the add_id column

The following SQL query is steps 1-3 for "stock control requirements":

-- Step 1: Retrieve the required columns for stock control analysis

SELECT

    s1.item_name AS item_name,

    s1.ing_name AS ing_name,

    s1.ing_id AS ing_id,

    s1.ing_weight AS ing_weight,

    s1.ing_price AS ing_price,

    s1.order_quantity AS order_quantity,

    s1.recipe_quantity AS recipe_quantity,

    (s1.order_quantity * s1.recipe_quantity) AS ordered_weight,

    (s1.ing_price / s1.ing_weight) AS unit_cost,

    ((s1.order_quantity * s1.recipe_quantity) * (s1.ing_price / s1.ing_weight)) AS ingredient_cost

FROM

    (

    -- Step 2: Subquery to calculate aggregated data for stock control analysis

    SELECT

        o.item_id AS item_id,

        i.sku AS sku,

        i.item_name AS item_name,

        r.ing_id AS ing_id,

        ing.ing_name AS ing_name,

        ing.ing_weight AS ing_weight,

        ing.ing_price AS ing_price,

        SUM(o.quantity) AS order_quantity,

        r.quantity AS recipe_quantity

    FROM

        orders o

        LEFT JOIN item AS i ON o.item_id = i.item_id

        LEFT JOIN recipe AS r ON i.sku = r.recipe_id

        LEFT JOIN ingredient AS ing ON ing.ing_id = r.ing_id

    GROUP BY

        o.item_id,

        i.sku,

        i.item_name,

        r.ing_id,

        r.quantity,

        ing.ing_weight,

        ing.ing_price

    ) s1;

The following SQL query is steps 4-5 for "stock control requirements":

-- Step 1: Retrieve the required columns for stock control analysis

SELECT

    s2.ing_name,

    s2.ordered_weight,

    ing.ing_weight,

    inv.quantity,

    ing.ing_weight * inv.quantity AS total_inv_weight

FROM

    (

    -- Step 2: Subquery to calculate aggregated data for stock control analysis

    SELECT

        ing_id,

        ing_name,

        SUM(ordered_weight) 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;

The following SQL query is for "staff data requirements": 

-- Step 1: Retrieve the required columns for staff data analysis

SELECT

    r.date,

    s.first_name,

    s.last_name,

    s.hourly_rate,

    sh.start_time,

    sh.end_time,

    ((HOUR(timediff(sh.end_time, sh.start_time)) * 60) + (MINUTE(timediff(sh.end_time, sh.start_time)))) / 60 AS hours_in_shift,

    ((HOUR(timediff(sh.end_time, sh.start_time)) * 60) + (MINUTE(timediff(sh.end_time, sh.start_time)))) / 60 * s.hourly_rate AS staff_cost

FROM

    rota r

-- Step 2: Join staff/shift table on the rota table.

    LEFT JOIN staff s ON r.staff_id = s.staff_id

    LEFT JOIN shift sh ON r.shift_id = sh.shift_id;