Past Events

Description


During the most recent social event you attended, you suddenly realized that you forgot your USB drive at a previous event. You’re pretty sure that you had your flash drive with you just last week, which means that you probably lost it during one of the events of the last 7 days. You want to find all the events you attended during this period.

The list of events you’ve attended (including the most recent one) is stored in a table called Events. It has three columns:

  • id: the unique id of the event;
  • name: the name of the event;
  • event_date: the date of the event.

You want to come up with the list of all the events you attended over the past 7 days, except for the very last one (since you know you lost your flash drive before then). Return this list as a table with columns name and event_date sorted by event dates in descending order.

It is guaranteed that there is at most one event on any given day.

Example

For the following table Events

id name event_date
1 TGIF 2016-11-18
2 TGIF 2016-11-11
3 Weekly team meeting 2016-11-07
4 Weekly team meeting 2016-11-14

the output should be

name event_date
Weekly team meeting 2016-11-14
TGIF 2016-11-11
  • [execution time limit] 10 seconds (mysql)

Solution


1
2
3
4
5
6
7
8
9
/*Please add ; after each select statement*/
CREATE PROCEDURE pastEvents()
BEGIN
	SELECT name, event_date
    FROM Events, (select MAX(event_date) as maxdate FROM Events) t
    WHERE DATEDIFF(maxdate,event_date) <= 7
    ORDER BY event_date DESC
    LIMIT 1, 99999;
END