How to split 10% of data set to “control” and 90% to “test” for each group in MS SQL server

user_10849417 0
Viewed 420

Context: I have a table which has RetailerCode, CustomerID,Segment like below

RetailerCode    CID        Segment
A6005         13SVC15       High
A6005         19VDE1F       Low
A6005         1B3BD1F       Medium
A6005         1B3HB48       Medium
A6005         1B3HB49       Low
A9006         1B3HB40       High
A9006         1B3HB41       High
A9006         1B3HB43       Low
A9006         1B3HB46       Medium

Here, I would like to divide the data set in to control and test as below, For each RetailerCode, I have set of customers with each customer tagged to a segment. I need to divide in such a way that

For each retailer

  • 10% of their High customers to control and remaining 90% of their high customers to test.
  • 10% of their Medium customers to control and remaining 90% of their Medium customers to test.
  • 10% of their Low customers to control and remaining 90% of their Low customers to test.

I tried below code and I know its wrong.

select RetailerCode, CID,Segment 
(case when row_number() over (order by newid()) <= (select 0.1* count(*) from Table)
             then 'control'
             else 'test'
        end) as group
from Table
group by RetailerCode, CID,Segment
Order by RetailerCode

Can someone please help me with it? Thanks in advance

sql-server sql
2 Answers

You seem pretty close:

select RetailerCode, CID,Segment 
       (case when row_number() over (partition by segment order by newid()) <=
                  0.1 * count(*) over (partition by segment) 
             then 'control'
             else 'test'
        end) as group
from Table
Order by RetailerCode;

I don't see why a group by is needed.

user_11440350

percent_rank is based on rank & count:

select RetailerCode, CID,Segment 
(case when percent_rank() over (partition by segment order by newid()) <= 0.1
             then 'control'
             else 'test'
        end) as group
from Table
Order by RetailerCode

And ntile is based on row_number and count:

select RetailerCode, CID,Segment 
(case when ntile(10) over (partition by segment order by newid()) = 1
             then 'control'
             else 'test'
        end) as group
from Table
Order by RetailerCode
user_25279050
Related