Creating your own Snowflake function

The development team is back again, thanks to all of your hard work from before. They'd like to create a "leader board" in the application to rank gym members by the efficiency of their workouts. Efficiency is determined by the number of calories that are burned per minute of working out. The first step to provide this data is creating a function to determine efficiency. Good luck!

This exercise is part of the course

Data Types and Functions in Snowflake

Exercise instructions

Define a function called calories_per_minute with three parameters: start_time and end_time, which are both of the type TIMESTAMP, and calories_burned, which is a NUMBER.
Further update the calories_per_minute function to return a NUMBER.
Use the DATEDIFF function to find the number of minutes between the check-in and checkout timestamps and divide by the number of calories_burned to determine workout efficiency.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- calories_per_minute should take a start_time, end_time, and calories_burned
CREATE OR REPLACE FUNCTION ___(
    ___ TIMESTAMP, ___ ___, calories_burned ___
)

-- Make sure the function returns a NUMBER
RETURNS ___

AS

$$
-- Use DATEDIFF to calculate the efficiency of a workout
___(MINUTE, start_time, end_time) / calories_burned
$$;

Edit and Run Code

This exercise is part of the course

Data Types and Functions in Snowflake

IntermediateSkill Level

4.9+

Start Course for Free

Before you can change the world using Snowflake, you need to know the type of data you'll be working with. You'll start with text data, learning to use it in queries and define columns, plus discover how to retrieve column details from any table. Then you'll master numeric data types and date/time data, understanding precision, scale, and how to convert strings to timestamps.

Exercise 1: Text data types Exercise 2: Is that text data?Exercise 3: Working out in the big city Exercise 4: Defining text data Exercise 5: Numeric data types Exercise 6: Go with the FLOAT Exercise 7: Creating a retail table Exercise 8: Datetime data types Exercise 9: Building datetime values Exercise 10: Which timezone?Exercise 11: When did you check in?

Now that you know your Snowflake data inside-and-out, it's time to manipulate and transform it. You'll begin with text data manipulation using TRIM, SPLIT, and CONCAT, then master numeric operations like arithmetic and summary measures with SUM and AVG. You'll explore timestamp transformations by extracting components like year or hours, calculating intervals between dates, and create your own custom Snowflake functions (UDFs) to implement personalized logic.

Exercise 1: Manipulating text data Exercise 2: Managing display length Exercise 3: Transforming text data Exercise 4: Motivating member marketing Exercise 5: Numeric calculations Exercise 6: Comparison operators Exercise 7: Adjusting membership prices Exercise 8: What burns more calories?Exercise 9: Manipulating datetime data Exercise 10: Breaking down a timestamp Exercise 11: How long were you at the gym?Exercise 12: Making appointments Exercise 13: User-defined functions in Snowflake Exercise 14: Creating your own Snowflake function

Current Exercise

Exercise 15: Ranking gym members by efficiency

In this final chapter, you'll crank the heat up with Snowflake's more advanced functionality. You'll start with VARIANT data types (semi-structured data), learning to parse top-level and nested data using dot-notation and bracket-notation, then master common table expressions (CTEs) by building and using multiple CTEs. Finally, you'll wrap up by creating pivoted tables using the PIVOT keyword to transform data into an easy-to-digest format, ready for reports and analysis.

Exercise 1: Flattening Semi-Structured Data Exercise 2: Retrieving nested data Exercise 3: Working with personal info Exercise 4: Building a gym member description Exercise 5: Multiple common table expressions Exercise 6: Identifying high-performers Exercise 7: Comparing senior workouts Exercise 8: Pivoting Data Exercise 9: Evaluating workout length Exercise 10: Is CrossFit a popular workout?Exercise 11: Which workouts burn the most calories?Exercise 12: Congratulations!