Notes

-- In this query:

-- We select the product_id from the product table.
-- We concatenate the old_code from the code table with the hard-coded value 'XYZ' to generate the new code.
-- We select the price of the product from the product table.
-- This will create a new table new_code_table that contains the product ID, new code, and price of each product, based on the mappings defined -- in the code table.

CREATE TABLE IF NOT EXISTS new_code_table AS
SELECT
p.product_id,
CONCAT(c.old_code, 'XYZ') AS new_code,
p.price
FROM
product p
JOIN
code c ON p.code_id = c.code_id;

-- -----------------------------------------------------------------------------------------------

-- Suppose we have the following sensor_data table:

| facility_id | timestamp | sensor_value |
|-------------|---------------------|--------------|
| 1 | 2024-03-01 12:00:00 | 10.5 |
| 1 | 2024-03-01 12:02:30 | 12.3 |
| 2 | 2024-03-01 12:05:15 | 8.7 |
| 2 | 2024-03-01 12:08:45 | 11.2 |


INSERT INTO aggregation_table (facility_id, timeslot, average_value)
WITH time_slots AS (
SELECT
facility_id,
TIMESTAMP_TRUNC(timestamp, MINUTE, 5) AS timeslot,
AVG(sensor_value) AS average_value
FROM
sensor_data
GROUP BY
facility_id, timeslot
)
SELECT
facility_id,
timeslot,
average_value
FROM
time_slots;



-- the output will be:

| facility_id | timeslot | average_value |
|-------------|---------------------|---------------|
| 1 | 2024-03-01 12:00:00 | 11.4 |
| 2 | 2024-03-01 12:05:00 | 9.95 |


-- ----------------------------------------------------------------------------------

SELECT
e.employee_id,
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
STRING_AGG(p.project_name, ', ') AS projects_assigned,
COUNT(p.project_id) AS total_projects_assigned
FROM
employees e
LEFT JOIN
projects p ON e.employee_id = p.employee_id
GROUP BY
e.employee_id, e.first_name, e.last_name;