Week 29 - Snowpark & Python

Snowpark skills are going to be seen as more and more useful as time goes by, and for this reason, we're posting another Snowpark challenge!

This week, we're registering UDFs.

Your start-up code is below - it will create a table for you that has employees and their start dates.

create or replace file format frosty_csvtype = csvskip_header = 1field_optionally_enclosed_by = '"';create stage w29_stageurl = 's3://frostyfridaychallenges/challenge_29/'file_format = frosty_csv;list @w29_stage;create table week29 asselect t.$1::int as id,t.$2::varchar(100) as first_name,t.$3::varchar(100) as surname,t.$4::varchar(250) as email,t.$5::datetime as start_datefrom @w29_stage (pattern=>'.*start_dates.*') t;

You need to create a UDF that will produce a fiscal year for the start_date. The logic should be that if the month is from May onwards* then the fiscal year should be the current year + 1, otherwise, the current year.

For example:

2022-05-13 = FY23

2022-02-11=FY22

Once you've written your UDF, execute the following code:

data = session.table("week29").select(col("id"),col("first_name"),col("surname"),col("email"),col("start_date"),fiscal_year("start_date").alias("fiscal_year"))data.show()

and

data.group_by("fiscal_year").agg(col("*"), "count").show()

The result should look like, this:

And there you have it!

*because we said so.