This is Part 3 of the three part series detailing the ‘tricky SQL test’ I took to get a potential job. Read Part 1 of the series here and Part 2 here.
The Test
Given a table events with the following structure:
create table events (
event_type integer not null,
value integer not null,
time timestamp not null,
unique (event_type, time)
):
insert into events (event_type, value, time) values (2, 5, '2015-05-09 12:42:00');
insert into events (event_type, value, time) values (4, -42, '2015-05-09 13:19:57');
insert into events (event_type, value, time) values (2, 2, '2015-05-09 14:48:30');
insert into events (event_type, value, time) values (2, 7, '2015-05-09 12:54:39');
insert into events (event_type, value, time) values (3, 16, '2015-05-09 13:19:57');
insert into events (event_type, value, time) values (3, 20, '2015-05-09 15:01:09');
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 orderred by event_type (in ascending order).
For example, given the following data:
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 follow rowset:
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.
The names of the columns in the rowset don’t matter, but their order does.
