Cortex – Basic

Once upon a time, a few years ago, our Intern didn’t kick off his career with the legendary Professor Frosty. Instead, he embarked on his data journey with the incredible Data Superheroes!

While he wasn’t out there battling villains face-to-face, he played a crucial role in the ‘good fight’ by performing pristine data analysis. His mission? To uncover why villains were striking on specific dates!

Now, it’s your turn to step into the Intern’s shoes. Below is the startup code to see the crimerates of the city where our Intern was working. Can you crack the code and help the Data Superheroes keep the city safe?

Startup Code

-- Create the table with an additional date column
CREATE OR REPLACE TABLE base_data (
    id INT AUTOINCREMENT,
    value INT,
    date TIMESTAMP_NTZ
);

-- Define the start date as a constant
SET start_date = '2020-01-01';

-- Insert 1000 records with values between 1 and 5 and increasing daily dates
INSERT INTO base_data (value, date)
WITH seq AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1 AS i
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 1000))
)
SELECT 
    UNIFORM(1, 5, RANDOM())::INT, 
    DATEADD(DAY, i, to_date($start_date))
FROM seq;

-- Insert 100 records with values between 1 and 5 and increasing daily dates
INSERT INTO data_to_inspect (value, date)
WITH seq AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1 AS i
    FROM 
        TABLE(GENERATOR(ROWCOUNT => 1000))
)
SELECT 
    UNIFORM(1, 5, RANDOM())::INT, 
    DATEADD(DAY, i, to_date($start_date))
FROM 
    seq;

-- Insert 2 records with values greater than 35 and increasing daily dates
INSERT INTO data_to_inspect (value, date)
VALUES
(36, DATEADD(DAY, 1000, to_date($start_date))),
(37, DATEADD(DAY, 1001, to_date($start_date)));

2 dates sprang out to the Intern , can you verify his calculations and point out which 2 dates stood out using anomaly detection (The Intern also specified that IS_ANOMALY might not be the only column you’d need to be sure)?

Reader Interactions

Leave a Reply Cancel reply