sample of data within groups in Teradata

Question

Suppose I have a table of week_nbr and cust_id.

Suppose I want a sample of 500 customers from each week.

The dumb way is to do this for each week:

select cust_id
from week_cust
sample randomized allocation 500
where week_nbr=1

What's the smart way? I.e., is there a way to make the following concept work?

select week_nbr
      ,random sample of 500 cust_id in this week
from week_cust

This is on Teradata 12.

Best, and thanks in advance.

Answer 1

It doesn't look to me as though it's possible to do this using the sample clause. You should be able to achieve the same result with something like this though:

select week_nbr, cust_id
from week_cust
qualify row_number() over (partition by week_nbr order by random(1, 10000)) <= 500