Question 313189 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql raffle draw query

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;
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    There’s no need to actually spawn the tickets and select from among them. The amount already dictates the probability. Demo at db<>fiddle:

    select row_number()over(order by 1/random()^amount) desc),
       user_id 
from tickets 
limit 5;

    It’s nice to have a purely arithmetic solution but this will quickly run into a numeric underflow problem when you hit high enough amount (like 56).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search