Pick 5 Customers from the Customer's Table Randomly to win Raffle

Question

I'm having trouble figuring out how to select only 5 people from my list, that are also selected by random.

The customers table has a customer_names(var) column and has 739 rows.

Here's what I have so far:

select
    customer_name
    ,rank() over (order by customer_name)
    ,floor(random()*(739-1)+1) as random_int
    ,case when (rank() over (order by customer_name)) = (floor(random()*(739-1)+1)) then 'Winner'
        else 'Not Selected'
    end as Raffle
from customer
order by raffle desc;

Here is a photo of what I get when I run this: Query

I was expecting to see that when the random int and the rank number = the same number, then the raffle column would show Winner. What happened instead is that the rank and random int don't match, and it will still say winner. I assume because the random() is being ran twice instead of being viewed as the same function. I tried calling to the casted name, "random_int" but it says the column doesn't exist. Thus I am stumped on three things:

1. How can I get it to say winner only when the rank and the random int column match
2. How can I then have it always create 5 winners
3. How can I only pull the winners (when I try using the where clause it says I can't do to rank() being a window function)

Thanks so much in advance, I am still learning SQL so sorry as well if there is an easy fix I am not seeing here.

Answer 1

If you just want 5 random people, it is quite easy in PostgreSQL:

select * from customer order by random() limit 5

If you want something else, you need to be clearer about what it is.

If you really want to consult the same random number twice (for each row), then you would use a subquery to do that, but it hard to see how this advances your goal:

select *, random_int=rank as winner from (
    select
        customer_name
        ,rank() over (order by customer_name) as rank
        ,floor(random()*(739-1)+1) as random_int
    from customer
) foobar order by winner desc;