postgresql raffle draw query

Question

goal draw first place to fifth place tickets. a user can't win multiple places. If a user hasn't placed, then all of their tickets must be considered in the draw.

progress I can draw one winner, but not all five.

idea I could repeat the query 4 more times and eliminate the previous user_ids each time. But there's got to be a better way.

question how do I draw all 5 place tickets?

tickets table

• id
• user_id
• amount
• created_at

this is my query

WITH gen_tickets AS (
    -- Use `generate_series()` to create a row for each ticket
    SELECT user_id, amount, created_at
    FROM tickets
    CROSS JOIN LATERAL generate_series(1, CAST(amount AS INTEGER))
),
shuffle AS (
    SELECT user_id, amount, row_number() OVER (ORDER BY random()) AS rn
    FROM gen_tickets
)
SELECT * FROM shuffle
ORDER BY RANDOM()
LIMIT 1;

Answer 1

You can use generate_series to create one roll of the dice per ticket. Then select the five users with the highest rolls:

SELECT  *
FROM    (
        SELECT  user_id
        ,       max(random()) as roll
        FROM    tickets
        CROSS JOIN
                generate_series(1, amount) gs(num)
        GROUP BY
                user_id
        ) s
ORDER BY
        roll DESC
LIMIT   5

Example at DB Fiddle