SQL Server Number Sequence Quartile

Çağlar Can SARIKAYA

Published in

.Net Programming

3 min readAug 5, 2021

Hi everyone, Today I want to speak about how eCommerce web pages define a price filter on their pages.

price-filter-example — Its a small prices category example

price-filter-example-2 — this one is from a mobile phone category

In my opinion, defining these filters statically should be hard. So I created a basic algorithm on the SQL server.

There are some SQL server functions for calculating rank. You can take a look here

I used NTILE function. Basically, you have to give a number like 4 and it will split the array into 4.

Let's create a table to see how it works;

create table PriceList (price int)insert into PriceList
select floor(rand()*1000)
go 100

then let's try the NTILE function

select 
price,
NTILE(4) OVER (ORDER BY price) AS Quartile --dividing4 all array
,PERCENT_RANK() OVER(ORDER BY price) -- dividing ratio
from PriceList

Everything seems to be fine, but we still need to set this splitting process to automatic. I used LOG function from SQL server.

The logarithm is taking the opposite of power, let's assume we have 100 element array, we can split this 4, but when element counts increasing we should increase the split number. I don't know is that the optimum way but taking log base e (I mean Ln) is enough in my opinion.

declare @count int
select @count = LOG(count(*)) from zz;with q as(
select
price,
NTILE(@count) OVER (ORDER BY price) AS Quartile
from zz
)
select
ROUND( MIN(price) , -2)
, ROUND(MAX(price) , -2)
,Quartile
from q
group by Quartile