[MySQL]Jakie klauzule wykorzystać do rozwiązania tego zadania? Opcje

[szczrzcz]

Dostałem zadanie na rozmowie na którym poległem, i które na spokojnie w domu też nie wiem jak ugryźć. Proszę o wskazówki jak można to rozwiązać.
Zacząłem od napisania

Kod

SELECT event_type, sum(value) as value FROM `events` GROUP BY event_type

co oczywiście jest dalekie od rozwiązania.



Given a table events with the following structure:

[SQL] pobierz, plaintext 
 CREATE TABLE events (
 event_type integer NOT NULL,
 value integer NOT NULL,
 time timestamp NOT NULL,
 UNIQUE(event_type, time)
 );
[SQL] pobierz, plaintext 

write an SQL query that, for each event_type that has been registered more than once, returns the difference between the latest (i.e. the most recent in
terms of time) and the second latest value. The table should be ordered by event_type (in ascending order).
For example, given the following data:

Kod

event_type | value | time
------------+------------+--------------------
2              | 5        | 2015-05-09 12:42:00
4              | -42     | 2015-05-09 13:19:57
2              | 2        | 2015-05-09 14:48:30
2              | 7        | 2015-05-09 12:54:39
3              | 16      | 2015-05-09 13:19:57
3              | 20      | 2015-05-09 15:01:09

your query should return the following rowset:

Kod

event_type | value
------------+-----------
2              | -5
3              | 4

For the event_type 2, the latest value is 2 and the second latest value is 7, so the difference between them is −5.