SQL Server 2008 string math

Well, after a long time without a post, I'm back here with some advanced SQL tips.

Today in my service, I came across a situation in which the programming language was being ineffective to perform a mathematical calculation of CPC (Cost Per Click), since it has several combinations such as ad views versus clicks obtained. I realized then that the process was slow with a simple 5 registered ads (seen more than 10,000 times each). So I decided to think and despite not trusting the results that SQL Server brings, I decided to try. And what was the result? Fantastic. It is very simple to do.

Assuming two tables, Advertising_Visualization (id pk, campaign int fk, ad int fk, datetime preview, url varchar, etc.) and another Advertising_Clicks with similar fields.

Now, the line that saved my day was:

"SELECT (SELECT COUNT (id) FROM Advertising_Cliques where ad = [= id]) / (SELECT COUNT (id) FROM Advertising_Views where ad = [= id]) * 100"

Since [= id] is the same as the ad variable that you want to get the percentage amount of results.

Okay, this was the line that performed the processing almost 90% faster on the page, opening almost instantly for several queries like this (for each record in another query).

With the percentage in hand, it is possible to calculate the CPC easily. Just calculate the gross value of the ad by dividing by 100 and multiplying by the desired percentage to really know how much each click cost. (1000 views, 10 clicks (1%). Ad value: $ 10/100 = $ 0.10 * 10 clicks = $ 1.00.)