Uncategorized

create table home_sales (
sale_date date,
price number(11, 2)
);

insert into home_sales (sale_date, price) values
(‘2013-08-01’::date, 290000.00),
(‘2014-02-01’::date, 320000.00),
(‘2015-04-01’::date, 399999.99),
(‘2016-04-01’::date, 400000.00),
(‘2017-04-01’::date, 470000.00),
(‘2018-04-01’::date, 510000.00);

CREATE OR REPLACE TABLE week_14 (
    superhero_name varchar(50),
    country_of_residence varchar(50),
    notable_exploits varchar(150),
    superpower varchar(100),
    second_superpower varchar(100),
    third_superpower varchar(100)
);

INSERT INTO week_14 VALUES ('Superpig', 'Ireland', 'Saved head of Irish Farmer\'s Association from terrorist cell', 'Super-Oinks', NULL, NULL);
INSERT INTO week_14 VALUES ('Señor Mediocre', 'Mexico', 'Defeated corrupt convention of fruit lobbyists by telling anecdote that lasted 33 hours, with 16 tangents that lead to 17 resignations from the board', 'Public speaking', 'Stamp collecting', 'Laser vision');
INSERT INTO week_14 VALUES ('The CLAW', 'USA', 'Horrifically violent duel to the death with mass murdering super villain accidentally created art installation last valued at $14,450,000 by Sotheby\'s', 'Back scratching', 'Extendable arms', NULL);
INSERT INTO week_14 VALUES ('Il Segreto', 'Italy', NULL, NULL, NULL, NULL);
INSERT INTO week_14 VALUES ('Frosty Man', 'UK', 'Rescued a delegation of data engineers from a DevOps conference', 'Knows, by memory, 15 definitions of an obscure codex known as "the data mesh"', 'can copy and paste from StackOverflow with the blink of an eye', NULL);

create or replace table testing_data(id int autoincrement start 1 increment 1, product string, stock_amount int,date_of_check date);
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',1,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',2,'2022-01-02');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',NULL,'2022-02-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',NULL,'2022-03-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',5,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-13');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',6,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',NULL,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',3,'2022-04-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',2,'2022-07-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',NULL,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',3,'2022-05-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',NULL,'2022-10-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',10,'2022-11-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-14');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-15');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-13');

create schema <enter_db_name_here>.world_bank_metadata;

create or replace table <enter_db_name_here>.world_bank_metadata.country_metadata
(
    country_code varchar(3),
    region string,
    income_group string
);

create schema <enter_db_name_here>.world_bank_economic_indicators;

create or replace table <enter_db_name_here>.world_bank_economic_indicators.gdp
(
    country_name string,
    country_code varchar(3),
    year int,
    gdp_usd double
);

create table <enter_db_name_here>.world_bank_economic_indicators.gov_expenditure
(
    country_name string,
    country_code varchar(3),
    year int,
    gov_expenditure_pct_gdp double
);

create schema <enter_db_name_here>.world_bank_social_indiactors;

create or replace table <enter_db_name_here>.world_bank_social_indiactors.life_expectancy
(
    country_name string,
    country_code varchar(3),
    year int,
    life_expectancy float
);

create or replace table <enter_db_name_here>.world_bank_social_indiactors.adult_literacy_rate
(
    country_name string,
    country_code varchar(3),
    year int,
    adult_literacy_rate float
);

create or replace table <enter_db_name_here>.world_bank_social_indiactors.progression_to_secondary_school
(
    country_name string,
    country_code varchar(3),
    year int,
    progression_to_secondary_school float
);

-- Set the database and schema
use database <insert_db_name_here>
use schema <insert_schema_name_here>

-- Create the stage that points at the data.
create stage week_11_frosty_stage
    url = 's3://frostyfridaychallenges/challenge_11/'
    file_format = <insert_csv_file_format;

-- Create the table as a CTAS statement.
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;


-- TASK 1: Remove all the centrifuge dates and centrifuge kwph and replace them with NULLs WHERE fat = 3. 
-- Add note to task_used.
create or replace task whole_milk_updates
    schedule = '1400 minutes'
as
    <insert_sql_here>


-- TASK 2: Calculate centrifuge processing time (difference between start and end time) WHERE fat != 3. 
-- Add note to task_used.
create or replace task skim_milk_updates
    after frosty_friday.challenges.whole_milk_updates
as
    <insert_sql_here>


-- Manually execute the task.
execute task whole_milk_updates;

-- Check that the data looks as it should.
select * from week11;

-- Check that the numbers are correct.
select task_used, count(*) as row_count from week11 group by task_used;

-- Create the warehouses
create warehouse if not exists my_xsmall_wh 
    with warehouse_size = XSMALL
    auto_suspend = 120;
    
create warehouse if not exists my_small_wh 
    with warehouse_size = SMALL
    auto_suspend = 120;

-- Create the table
create or replace table <table_name>
(
    date_time datetime,
    trans_amount double
);

-- Create the stage
create or replace stage week_10_frosty_stage
    url = 's3://frostyfridaychallenges/challenge_10/'
    file_format = <enter_file_format>;

-- Create the stored procedure
create or replace procedure dynamic_warehouse_data_load(stage_name string, table_name string)
<code goes here>

-- Call the stored procedure.
call dynamic_warehouse_data_load('week_10_frosty_friday_stage', '<table_name>');