My goal was just to create, test and use a relatively simple SQL query. Some complexity comes from aggregate functions and table self joins only.
I had 2 tables, article and view, where I store view count. I wanted to calculate a relative measure of read frequency, instead of absolute view counts. So recently published content is more comparable to older material.
To normalize this, we can calculate a views-per-day ratio and then compare all articles against the highest ratio one.
In this article, we’ll walk through:
- Showcasing the schema and the goal to achieve.
- Building the query that calculates relative read frequency.
- Investigating indexing strategies based on execution plans.
Continue reading Optimizing Relative Read Frequency Queries in SQL