by 
The Intern has been very glad lately with his new position together with Professor Frosty. Before joining F_F, he was stuck at a job that had a particularly nasty manager.
Using the tools Snowflake Cortex and Top_insights, can you figure out which manager was causing so much trouble?
Start-up Code
CREATE OR REPLACE TABLE employees (
employee_id INT AUTOINCREMENT,
level VARCHAR(2) DEFAULT 'L1',
manager_id INT,
base_salary INT,
time_with_company INT,
measurement_date DATE
);INSERT INTO employees (manager_id, base_salary,time_with_company, measurement_date)
WITH emp_data_control AS (
SELECT
UNIFORM(1, 20, RANDOM()) AS manager_id,
CASE
WHEN RANDOM() < 0.3 THEN UNIFORM(3500, 4499, RANDOM())
ELSE UNIFORM(4500, 5500, RANDOM())
END AS base_salary,
UNIFORM(6, 20, RANDOM()) AS time_with_company,
DATE_FROM_PARTS(2024, 1, 1) AS measurement_date
FROM
TABLE(GENERATOR(ROWCOUNT => 1000))
)
SELECT * FROM emp_data_control;INSERT INTO employees (manager_id, base_salary,time_with_company, measurement_date)
WITH managers AS (
SELECT
SEQ4() AS manager_id,
CASE
WHEN SEQ4() IN (10) THEN 'high_negative'
ELSE 'high_positive'
END AS impact
FROM
TABLE(GENERATOR(ROWCOUNT => 20))
),
emp_data AS (
SELECT
m.manager_id AS manager_id,
CASE
WHEN RANDOM() < 0.3 THEN UNIFORM(4000, 4999, RANDOM())
ELSE UNIFORM(5000, 6000, RANDOM())
END AS base_salary,
CASE
WHEN m.impact = 'high_negative' THEN UNIFORM(1, 5, RANDOM())
ELSE UNIFORM(6, 20, RANDOM())
END AS time_with_company,
DATE_FROM_PARTS(2024, 10, 10) AS measurement_date
FROM
TABLE(GENERATOR(ROWCOUNT => 1000))
JOIN
managers AS m ON true
ORDER BY RANDOM()
)
SELECT * FROM emp_data;Intern has given you 2 pieces of data inside of a single table. One of them is control data, and the other is testing data.
- Control data is from 2024-01-01 when everything seemed normal
- Testing data from 2024-10-10.
Can you point out which manager seems to be having a very high turnover rate?
It was great catching up with the Frosty Friday team at Summit last week! Thanks for another fun challenge