PostgreSQL User-Defined Function (UDF) for Week-Ending Saturday

Acronym's are GREAT! Not only do they make you seem extremely shrewd and brainy, they also are also are a great way to confuse your enemies and coworkers. Let's all add another cool acronym to our verbiage.

If you don't know about User-Defined Functions (UDF's) then get with it! They are available in almost every flavor of SQL, including PostgreSQL and Redshift. UDF's allow you to create your own SQL functions, aka a reusable block of code. In more practical terms, they allow us embed business logic in a single place, rather than copy and pasting it across multiple queries and procedures. They also help us keep our code DRY (another great acronym!).

Let's do a run through of a good UDF use-case. Imagine, we are super important traders and want to analyze the S&P. We hope to develop a swing trading strategy, which involves holding a position for multiple days to weeks. Therefore, we need to not only look at the data on a daily basis, but also on a weekly basis.

For some reason I can't explain away, we also want our week to be based on Saturday-Friday instead of Monday-Sunday. If it were Monday, we could use the date_trunc function, built into both PostgreSQL and Redshift. But we don't want Monday, we want Saturday!

So how do we solve this? Create our own UDF!

Our UDF will find the number for the day of week (O for Sunday, 1 for Monday, etc.). It then subtracts that number plus + 1 days from the date, except if it's Saturday, in which case we subtract 0. Here's the code:

CREATE FUNCTION week_ending_saturday(date) RETURNS date AS $$
  SELECT $1 - CASE
                WHEN DATE_PART('dow', $1) = 6
                  THEN 0
                  ELSE (DATE_PART('dow', $1) + 1)::INT
                END
$$ LANGUAGE SQL;

If that all seems confusing to you, then you're in luck! I put together the following chart to illustrate step-by-step how the function works.

Enough theory! Let's see this UDF in action.

SELECT
  date,
  week_ending_saturday(date),
  to_char(date, 'Day') as weekday

FROM visualizations.stock_prices

ORDER BY date DESC

LIMIT 15;

The row highlight colors corrspond to different week_ending_saturday's. Now let's look a our actual use case for this:

WITH date_filter AS (
  SELECT
    max(week_ending_saturday(date)) - 7 * 4 AS date_start,
    max(week_ending_saturday(date)) AS date_end
  
  FROM visualizations.stock_prices
)
SELECT
  week_ending_saturday(date),
  TO_CHAR(min(close), 'L9,999') AS min_closing_price,
  TO_CHAR(avg(close), 'L9,999') AS average_closing_price,
  TO_CHAR(max(close), 'L9,999') AS max_closing_price,
  COUNT(*) AS trading_days
  
FROM visualizations.stock_prices

LEFT JOIN date_filter
       ON 1 = 1

WHERE date between date_start and date_end

GROUP BY 1

ORDER BY 1;

We can see the havoc Corona Virus has wrecked in this chart! The week of 2/15 was higher but the index fell of a cliff during the week of 2/22! If we believe this some part of larger market trend, we'll have to develop a short startegy or maybe keep our money in cash for a bit.

Hope this was helpful! Go forth and make UDFs!