Holiday Event
Description
The store you’re working for has decided to organize a special holiday event: every 4th
purchase made during this event grants the lucky person who made it a special prize.
All purchases made during the event are stored in the database table purchases with the following structure:
- timestamp: unique purchase timestamp;
- buyer_name: the name of the person who made this purchase.
Given the purchases table, compose the resulting table with one column winners
containing the names of the buyers who won the special prize by making a purchase number k \* 4
for some integer k
. The numbering of the purchases starts with 1
.
Note, that each person can get no more than one prize (i.e. their name can occur in the answer at most once).
The table should be sorted by the winners’ names in ascending order.
Example
For the following tables purchases
timestamp | buyer_name |
---|---|
2014-11-09 15:23:05 | Frank West |
2014-11-09 20:11:02 | Terrence Alexander |
2014-11-10 12:10:00 | Sandy Cohen |
2014-11-10 13:00:11 | Frank West |
2014-11-10 14:09:10 | Sandy Cohen |
2014-11-10 14:15:15 | Leonard Grant |
2014-11-10 17:09:10 | Frank West |
2014-11-10 19:09:10 | Diane Tucker |
2014-11-11 18:09:11 | Pauline Ross |
2014-11-11 20:00:00 | Jasmine Gibson |
2014-11-12 10:12:00 | Kim Neal |
2014-11-12 10:12:01 | Frank West |
2014-11-12 15:14:42 | Sean Kim |
the output should be
winners |
---|
Diane Tucker |
Frank West |
because Frank West made the 4th
and the 12th
purchases, and Diane Tucker’s purchase was the 8th
.
- [execution time limit] 10 seconds (mysql)
Solution
1
2
3
4
5
6
7
8
9
10
11
/*Please add ; after each select statement*/
CREATE PROCEDURE holidayEvent()
BEGIN
SET @purchase = 0;
SELECT DISTINCT buyer_name AS winners
FROM(
SELECT * FROM purchases
WHERE (@purchase:=@purchase + 1)%4 = 0
ORDER BY timestamp) t
ORDER BY buyer_name;
END