Filtering values within a partition

Question

I have a dataset of revenue values of table revenues.

I am required to calculate the standard deviation for each day looking at 60 days back prior to current date. When the standard deviation is calculated, and I run it as follows:

SELECT
    [Date],Brand,[Country],[Marketing Channel],
    STDEV(Revenue) OVER (PARTITION BY Brand, Country,[Marketing Channel] 
        ORDER BY [Date] ROWS BETWEEN 2 PRECEDING and 1 PRECEDING) 
    as STD
FROM RevTable

The problem is that extreme values within the partition tip the standard deviation up or down. I wish to filter extreme values within the partition itself.

How can I filter out the extreme values in such structure (percentile based filtering is what I had in mind) ?

Date	Brand	Country	Marketing Channel	Revenue
1/1/2021	FunGames	Canada	SEO	$ 3,578,834
1/1/2021	FunGames	Canada	Social Networks	$ 90,000
1/1/2021	FunGames	Netherlands	SEO	$ 682,943
1/2/2021	FunGames	Canada	SEO	$ 3,731,849
1/2/2021	FunGames	Canada	Social Networks	$ 257
1/2/2021	FunGames	Netherlands	SEO	$ 627,272
1/3/2021	FunGames	Canada	SEO	$ 2,418,136
1/3/2021	FunGames	Canada	Social Networks	$ 40
1/3/2021	FunGames	Netherlands	SEO	$ 479,642
1/4/2021	FunGames	Canada	SEO	$ 2,231,254
1/4/2021	FunGames	Canada	Social Networks	$ -
1/4/2021	FunGames	Netherlands	SEO	$ 635,715
1/5/2021	FunGames	Canada	SEO	$ 2,686,366
1/5/2021	FunGames	Canada	Social Networks	$ 177
1/5/2021	FunGames	Netherlands	SEO	$ 499,026
1/5/2021	FunGames	Netherlands	Social Networks	$ -
1/6/2021	FunGames	Canada	SEO	$ 2,096,472
1/6/2021	FunGames	Canada	Social Networks	$ 465
1/6/2021	FunGames	Netherlands	SEO	$ 653,359
1/6/2021	FunGames	Netherlands	Social Networks	$ -
1/7/2021	FunGames	Canada	SEO	$ 2,962,476
1/7/2021	FunGames	Canada	Social Networks	$ 663
1/7/2021	FunGames	Netherlands	SEO	$ 747,990
1/8/2021	FunGames	Canada	SEO	$ 3,092,163
1/8/2021	FunGames	Canada	Social Networks	$ 156
1/8/2021	FunGames	Netherlands	SEO	$ 655,688
1/8/2021	FunGames	Netherlands	Social Networks	$ -
1/9/2021	FunGames	Canada	SEO	$ 3,110,117
1/9/2021	FunGames	Canada	Social Networks	$ 153
1/9/2021	FunGames	Netherlands	SEO	$ 571,313
1/9/2021	FunGames	Netherlands	Social Networks	$ -
1/10/2021	FunGames	Canada	SEO	$ 3,024,675
1/10/2021	FunGames	Canada	Social Networks	$ 68
1/10/2021	FunGames	Netherlands	SEO	$ 462,699
1/10/2021	FunGames	Netherlands	Social Networks	$ 563
1/11/2021	FunGames	Canada	SEO	$ 2,552,153
1/11/2021	FunGames	Canada	Social Networks	$ 275
1/11/2021	FunGames	Netherlands	SEO	$ 725,954

Desired Output:

Date	Brand	Country	Marketing Channel	STD
1/1/2021	FunGames	Canada	SEO	522429
1/1/2021	FunGames	Netherlands	SEO	97543
1/2/2021	FunGames	Canada	Social Networks	27069
1/2/2021	FunGames	Netherlands	Social Networks	251.66

But without outliers within partition

user_1144035 · Answer

You don't explain what "extreme values" are. But you can just use a CASE expression. For instance, if you only wanted values between 10 and 100:

SELECT rt.*
       STDEV(CASE WHEN Revenue >= 10 AND Revenue <= 100 THEN Revenue END) OVER
             (PARTITION BY Brand, Country, [Marketing Channel] 
              ORDER BY [Date]
              ROWS BETWEEN 2 PRECEDING and 1 PRECEDING
             ) as STD
FROM RevTable rt ;

STDEV() -- as with most aggregation and window functions -- ignores NULL values.

Filtering values within a partition

1 Answers