PostgreSQL | Codility | SqlEventsDelta

PostgreSQL를 사용할 일이 생겨서 코딜리티 SQL 문제를 풀어봤다.

해커랭크에서 MySQL 버전 문제로 With 문이나 Window Function등의 사용 제약이 있었는데, PostgreSQL을 사용하니 해당 제약 없이 편리하게 쿼리를 짤 수 있었다.

 

 

서론은 마치고 본론.

아래부터 SqlEventsDelta의 문제이다.

Question

Compute the difference between the latest and the second latest value for each event type.

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.

Copyright 2009–2023 by Codility Limited. All Rights Reserved. 
Unauthorized copying, publication or disclosure prohibited.

event_type 별  time을 비교하여,
가장 최근 value 값과, 그 다음 두번째 최근 value 값의 차이를 구하는 쿼리를 작성하면 된다.

 

Solve

-- Implement your solution here
WITH temp AS (
SELECT event_type
     , value
     , time
     , row_number() over (partition by event_type order by time desc)  AS rnum
FROM events)

SELECT a.event_type
     , a.value - b.value
FROM (SELECT event_type,value FROM temp WHERE rnum = 1) AS a
    JOIN (SELECT event_type,value FROM temp WHERE rnum = 2) AS b
    ON  a.event_type = b.event_type
ORDER BY a.event_type

• Window 함수 활용
  • 최신, 두번째 최신값이 무엇인지 알 수 있도록 row_number() over() 함수를 활용해주었다. 이 때 rank() over() 를 활용해도 무방하다.
  • partition by 로 묶음 기준이 될 값을 선언해준다.
  • order by 로 timestamp 최신순으로 정렬한다. desc 명령을 통해 내림차순으로 정렬해주었다.
• With 문 활용
  • 최신순으로 번호가 매겨진 쿼리를 with문을 활용해 새로운 쿼리에서 편리하게 활용할 수 있다.
• Join 활용
  • With문으로 감싸진 쿼리에 최신 값, 두번째 최신값을 Join문으로 엮어 가져온 후, 두 값의 차이를 구하여 문제를 해결 할 수 있다.

---

Source

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

SqlEventsDelta coding task - Practice Coding - Codility