samthebrand
samthebrand

Reputation: 3090

How to sum if within percentile in SQL Server?

I have a table that looks something like this:

enter image description here

It contains more than 100k rows.

I know how to get the median (or other percentile) values per week:

SELECT DISTINCT week, 
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY visits) OVER (PARTITION BY week) AS visit_median
FROM table
ORDER BY week

But how do I return a column with the total visits within the top N percentile of the group per week?

Upvotes: 0

Views: 1205

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I don't think you want percentile_cont(). You can try using ntile(). For instance, the top decile:

SELECT week, SUM(visits)
FROM (SELECT t.*,
             NTILE(100) OVER (PARTITION BY week ORDER BY visits DESC) as tile
      FROM table
     ) t
WHERE tile <= 10
GROUP BY week
ORDER BY week;

You need to understand how NTILE() handles ties. Rows with the same number of visits can go into different tiles. That is, the sizes of the tiles differ by at most 1. This may or may not be what you really want.

Upvotes: 2

Related Questions