Wednesday, June 16, 2010

Designing a Lottery System

I was asked this question in an interview: You need to design a database to hold customers and their lottery tickets. A lottery ticket has a sequence of 6 numbers e.g. 04 12 18 28 35 41. Once you have designed your tables, write a query which will print out a report of all customers whose tickets match three or more numbers of the winning number. Assume every customer has only one lottery ticket.

The simplest way is to have two tables:

  • Customer: with an id, name etc
  • Ticket: with an id, customer id and number (which will hold one of the numbers only, so you will get six records per ticket)
Given a winning number, the query to find all customers and how many numbers they matched would be:
SELECT c.name, COUNT(t.num) AS matches
FROM customer c, ticket t
WHERE c.id = t.customer_id
AND t.num IN
(
'05', /*winning number*/
'12',
'19',
'28',
'35',
'42'
)
GROUP BY customer
HAVING COUNT(t.num) >= 3
ORDER BY matches DESC

1 comment:

  1. Mike Tyson Says This Man is a LEGEND

    Hey there,

    You gotta see this... Richard Lustig won the lottery so many times that even Mike Tyson is even hanging out with this guy.

    That's right... Mike Tyson was pictured with Richard Lustig multiple times, the world's only 7 time lotto game grand prize winner.

    The word is.. Richard is personally coaching Mike Tyson how to win the lotto multiple times like he did.

    >>> Watch This Free Video Before It's Gone <<<

    All the best
    Ana

    ReplyDelete