A Lesser Known SQL Function for Financial Data Analysis

Hey there!

So, I recently ran into this forgotten SQL function called SIGN(), this is a mathematical function that may not be commonly associated with financial analysis, but it can prove to be a powerful tool in certain scenarios.

The SIGN() function can return +1 when the specified expression is positive, -1 when the expression is negative or 0 when it’s zero. This function can be quite handy, particularly for those folks doing financial data analysis.

Let’s see an example using this function.

The Set Up

First I’ll create a table and insert some values that would simulate the daily profit or loss of a company for the last 180 days.

CREATE TABLE dbo.daily_profit
(
	date		DATE,
	profit		DECIMAL(10,2),
	revenue		DECIMAL(10,2),
	expenses	DECIMAL(10,2),
)

Very simple and straightforward table, with just four columns. Now, let’s generate 180 days of values can calculate the profits based on the formula profit = revenue - expenses

WITH dates AS
(
	SELECT 
		DATEADD(DD,-value, CAST(GETEDATE() AS DATE)) AS DATE,
		CAST(ROUND(1000 + (ABS(CHECKSUM(NEWID())) % 1000000)/100.0, 2) AS DECIMAL(10, 2)) AS revenue,
		CAST(ROUND(500 + (ABS(CHECKSUM(NEWID())) % 1000000)/100.0, 2) AS DECIMAL(10, 2)) AS expenses
	FROM 
		GENERATE_SERIES(1,180)
)
INSERT INTO dbo.daily_profit(date, revenue, expenses, profit)
SELECT
	*, revenue - expenses AS profit
FROM
	dates
ORDER BY 
	date ASC;

Let’s query the table and see what it looks like.

SELECT date, profit, revenue, expenses FROM dbo.daily_profit;

date       profit         revenue        expenses
---------- -------------- -------------- ------------
2024-02-21 1439.64        10461.24       9021.60
2024-02-20 -2334.20       2344.88        4679.08
2024-02-19 4244.82        10173.05       5928.23
2024-02-18 4418.97        5297.38        878.41
2024-02-17 1680.12        3247.45        1567.33
2024-02-16 -4091.95       1339.46        5431.41

The SIGN() Function in Financial Data Analysis

Using the SIGN() function, we can quickly categorize these records into profitable (where profit > 0), breakeven (where profit = 0), and loss making (where profit < 0). This categorization can help analysts to quickly understand the performance trends and to identify any outliers or significant changes in performance.

SELECT
	date,
	profit,
	CASE SIGN(profit)
		WHEN 1 THEN 'is profitable'
		WHEN 0 THEN 'breakeven'
		WHEN -1 THEN 'loss making'
	END AS performance
FROM
	dbo.daily_profit
ORDER BY
	date ASC;


date       profit                                  performance
---------- --------------------------------------- -------------
2023-08-26 2006.57                                 is profitable
2023-08-27 -1078.00                                loss making
2023-08-28 1798.61                                 is profitable
2023-08-29 3335.40                                 is profitable
2023-08-30 5861.58                                 is profitable
2023-08-31 -5517.12                                loss making
2023-09-01 7009.96                                 is profitable
2023-09-02 5017.27                                 is profitable
2023-09-03 -8588.00                                loss making
2023-09-04 -1485.25                                loss making
2023-09-05 -1314.14                                loss making
2023-09-06 -977.84                                 loss making

Tracking the Direction of Profit Change with the SIGN() Function

The SIGN() function can also be combined with other functions to create more complex financial indicators. For example, you can combine SIGN() with the LAG() function to track the direction of change in the profits.

You might be interested in knowing whether has increased, decreased or remained the same compared to the previous day.

SELECT
	date,
	CASE SIGN(profit - LAG(profit) OVER (ORDER BY date))
		WHEN  1 THEN 'Increased'
		WHEN 0 THEN 'No change'
		WHEN -1 THEN 'Decreased'
	END as profit_change_direction
FROM
  dbo.daily_profit

Date       ProfitChangeDirection
---------- ---------------------
2023-08-26 NULL
2023-08-27 Decreased
2023-08-28 Increased
2023-08-29 Increased
2023-08-30 Increased
2023-08-31 Decreased
2023-09-01 Increased
2023-09-02 Decreased
2023-09-03 Decreased
2023-09-04 Increased

Conclusion

The SIGN() function can play a pivotal role in financial analysis. Its simplicity and flexibility make it a valuable addition to your SQL toolkit.

Thank you for reading! Remember that you can get the code for this and all my previous posts in my Github.

Cheers! 🍻