S2. What's under the Snowflake(s)?

Let us add something new in What’s under the Snowflake(s)? In this post, we focus on the challenge of Week 31 – Basic from Frosty Friday

«Your end result should look like that» – Week 31, Basic

To quickly start: copy and paste the block of code below. This will be the basis for your table/view:

-- Create a new table and insert some data there
CREATE OR REPLACE TABLE w31(id INT, hero_name STRING, villains_defeated NUMBER);

INSERT INTO w31 VALUES
  (1, 'Pigman', 5),
  (2, 'The OX', 10),
  (3, 'Zaranine', 4),
  (4, 'Frostus', 8),
  (5, 'Fridayus', 1),
  (6, 'SheFrost', 13),
  (7, 'Dezzin', 2.3),
  (8, 'Orn', 7),   
  (9, 'Killder', 6),   
  (10, 'PolarBeast', 11);

How to proceed? A possible strategy

After having executed the above statement, we need to discover the ‘Snowflake super hero’ with the hightest and the lowest numbers of villains_defeated.

1P: Find the maximum and the minimum value

To set the goal of this challenge we need to find the rows of TABLE ‘w31’ which containing the minimum or maximum value. Having set that as input, it should return the value of another column in that row.

The table ‘w31’ contains the columns id (int), hero_name (string), villains_defeated (numbers). MAX_BY (hero_name, villains_defeated) returns the value of the hero_name column for the row that has the highest value in the villains_defeated column. The same holds for the MIN_BY, but in this case, it returns the lowest value.

-- The following statement returns the hero_name of the 'Snowflake super hero' with the highest and lowest numerbs of villains_defeated:

SELECT MAX_BY(hero_name, villains_defeated) AS BEST_HERO,
       MIN_BY(hero_name, villains_defeated) AS WORST_HERO
FROM w31;

For intermediate or expert Snowflake’s users, there is a clear and interesting blog about some techical aspects of MAX_BY. Click here …this is highly recommended 🙂