Semana 11 – Básico

-- Establecer la base de datos y el esquema
use database <insert_db_name_here>
use schema <insert_schema_name_here>


-- Crea el escenario que apunta a los datos.
create stage week_11_frosty_stage
    url = 's3://frostyfridaychallenges/challenge_11/'
    file_format = <insert_csv_file_format;


-- Cree la tabla como una sentencia CTAS.
create or replace table frosty_friday.challenges.week11 as
select m.$1 as milking_datetime,
        m.$2 as cow_number,
        m.$3 as fat_percentage,
        m.$4 as farm_code,
        m.$5 as centrifuge_start_time,
        m.$6 as centrifuge_end_time,
        m.$7 as centrifuge_kwph,
        m.$8 as centrifuge_electricity_used,
        m.$9 as centrifuge_processing_time,
        m.$10 as task_used
from @week_11_frosty_stage (file_format => '<insert_csv_file_format>', pattern => '.*milk_data.*[.]csv') m;



-- TAREA 1: Elimine todas las fechas de centrifugado y los kwph de centrifugado y sustitúyalos por NULLs 
WHERE fat = 3. 
-- Añadir nota a task_used.
create or replace task whole_milk_updates
    schedule = '1400 minutes'
as
    <insert_sql_here>



-- TAREA 2: Calcular el tiempo de procesamiento de la centrifugadora (diferencia entre la hora de inicio y la hora final) 
WHERE fat != 3. 
-- Añadir nota a task_used.
create or replace task skim_milk_updates
    after frosty_friday.challenges.whole_milk_updates
as
    <insert_sql_here>



-- Ejecutar manualmente la tarea.
execute task whole_milk_updates;


-- Comprueba que los datos son correctos.
select * from week11;


-- Comprueba que los números son correctos.
select task_used, count(*) as row_count from week11 group by task_used;