[Codility] SqlEventsDelta - SQL(PostgreSQL)

끝이 없는 코테 공부... SQL도 그 문법이 조금조금 다른데 코딜리티 연습을 해보면서 PostgreSQL로 갈아탔다. 역시 With문을 쓰는 것이 더 깔끔하고 쉬운 것 같다.

코딜리티의 경우, 해석이 많이 돌아다니지 않는 듯 하여 답변을 정리해 놓기로 했다.

 

 

---

 

🤔 문제

https://app.codility.com/programmers/trainings/6/sql_events_delta/

SqlEventsDelta coding task - Practice Coding - Codility

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)
  );
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:

   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:

   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.

몇 안되는 코딜리티의 Exercises 6 > SQL 문제이고,Easy단계이다.

 

😄 나의 풀이

-- write your code in PostgreSQL 9.4
with rank_events as (
select event_type, value, time, rank() over(partition by event_type order by time desc) rank
from events
)
select A.event_type, A.value-B.value
from (select * from rank_events where rank =1) A
join (select * from rank_events where rank =2) B on A.event_type = B.event_type
order by A.event_type

with를 사용하여 events 테이블에서 시간의 순위를 매기는 테이블을 만들었다. 이때 partition by를 사용하여 event_type별 순위를 집계하였다. 그 다음 가장 최근, 최근 바로 전에 해당하는 이벤트의 value값 차를 구해주면 끝 !