SQL 코딩테스트 문제 풀이 (4) - 세션 재정의하기 (solvesql, CTE, LAG, ROW_NUMBER, JULIANDAY, 누적합, STRFTIME, 윈도우 함수)

📝목차

1. SQL 코딩테스트 문제 소개 - 세션 재정의하기 (solvesql)
2. 풀이 1 - LAG 윈도우 함수 사용, CHAT-GPT 활용
3. 풀이 2 - ROW_NUMBER 윈도우 함수 사용, 혼자 풀기
4. SQL 시간 변수 다루기 - JULIANDAY와 STRFTIME

 

정답 바로가기

 

SQL 코딩테스트 문제 풀이  네번째 시간이다.

1. SQL 코딩테스트 문제 소개 - 세션 재정의하기 (solvesql)

이번 문제는 solvesql 사이트> 데이터리안 SQL 캠프 실전반> 2페이지에서 접할 수 있다.

난이도는 5로 윈도우 함수를 적절히 활용해야 하고, CTE도 여러 번 사용하기 때문에 복잡하고 어렵다.

특히 LAG와 누적합을 사용하지 않고 풀으면 매우 복잡해진다.(내 얘기다...)

 

문제 바로가기

https://solvesql.com/problems/redefine-session/

 

solvesql 사이트는 해설을 따로 제공하지 않기 때문에 본 글을 통해 도움을 얻길 바란다!

https://solvesql.com/problems/redefine-session/

 

해당 문제는

1. 특정 사용자의 로그만 필터링한 뒤,
2. 직전 이벤트 시과 차이를 구하고,
3. 차이가 60분 이상인지에 따라 세션의 시작과 끝 지점을 찾아서 출력하는 것이다.

원래는 30분을 기준으로 세션이 정의되어있지만, 이것을 60분으로 재정의하면 된다.

 

주의점

세션의 끝을 60분 동안 아무 활동이 없을 때로 정의한다고 되어있다. 따라서 나는 마지막 로그에 60분을 더해서 세션의 끝이라고 했다. 그런데 그냥 60분 동안 아무 활동이 없으면 그 마지막 로그가 세션의 끝이 된다. 이 점을 유의하길 바란다!

 

2. 풀이 1 - LAG 윈도우 함수 사용, CHAT-GPT 활용

1) WHERE과 CTE(WITH ~ AS)를 사용하여 해당 유저의 기록 추출

WITH f AS (  -- 대상 사용자 필터
  SELECT
    user_pseudo_id,
    event_timestamp_kst
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
)

 

괄호 안 부분만 실행할 경우 다음과 같은 결과가 나타난다.

실행 결과

 

 

WHERE를 사용해서 ID가 'S3WDQCqLpK'인 유저의 로그만 추출한 뒤, CTE를 사용해서 f라는 이름으로 저장해 둔다. 결과에 필요한 컬럼은 유저 아이디(user_pseudo_id)와 이벤트 로그 기록 시각(event_timestamp_kst) 뿐이기 때문에 두 가지 컬럼만 추출(select)하면 된다.

 

CTE는 일부 쿼리의 실행 결과를 저장할 수 있는 방법으로 설명과 예제는 아래의 글을 참고하길 바란다.

SQL 코딩테스트 문제 풀이(2) - 가구 판매의 비중이 높았던 날 찾기(solvesql, CTE, CASE WHEN, COUNT, SQL 처

 

2) LAG 윈도우 함수 사용하여 직전 이벤트 로그 시각 구하기

WITH f AS (  -- 대상 사용자 필터
  SELECT
    user_pseudo_id,
    event_timestamp_kst
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
---------------추가된 부분---------------------
o AS (  -- 이전 이벤트 시각 구하기
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    LAG(event_timestamp_kst) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp_kst
    ) AS prev_ts
  FROM f
)

 

추가된 부분의 괄호 안 쿼리만 실행하면 다음 결과가 나타난다.

실행 결과

 

LAG 윈도우 함수를 사용하면 이전 행의 값을 가져올 수 있다. 직전 행의 시각을 가져온 뒤 차이를 구하면 60분이 지났는지를 확인할 수 있다. 위의 쿼리에서 LAG 부분을 설명하면 다음과 같다.

PARTITION BY user_pseudo_id	유저 id에 따라 그룹으로 분류한 뒤 (여기선 하나의 id에 대한 것이므로 전체)
ORDER BY event_timestamp_kst	시간 순서대로(오름차순) 정렬한 뒤
LAG(event_timestamp_kst)	직전 행의 event_timestamp_kst 값을 가져와라

 

LEAD를 사용하면 직전이 아닌 직후의 값을 가져올 수 있다.

LAG 추가설명

LAG 기본 개념

• 같은 결과 집합(row 그룹)에서 이전 행의 값을 현재 행 옆에 가져오는 분석(analytic) 함수
• 자기 조인 없이 “이전 값”을 쉽게 참조할 수 있어 변화 감지, 간격 계산, 세션 분리 등에 많이 사용

문법 (SQLite)

LAG(value_expr [, offset [, default_expr]]) OVER ( [PARTITION BY exprs] ORDER BY sort_exprs )

• value_expr : 이전 행에서 가져올 컬럼/표현식
• offset (선택) : 몇 행 이전을 볼지 (기본값 1)
• default_expr (선택) : 범위를 벗어났을 때(이전 행이 없을 때) 반환할 값 (기본값 NULL)
• OVER (...) :
  • PARTITION BY : 파티션(그룹) 경계. 각 파티션 안에서만 이전 행을 찾음
  • ORDER BY : “이전/다음”의 기준이 되는 정렬. 사실상 필수(없으면 결과가 비결정적).

참고: 윈도우 프레임(ROWS BETWEEN ...)은 LAG/LEAD에는 의미가 없다. (있어도 무시되거나 허용 안 됨)

사용 패턴

1) 바로 이전 값 붙이기

SELECT
  user_pseudo_id,
  event_timestamp_kst,
  LAG(event_timestamp_kst) OVER (
    PARTITION BY user_pseudo_id
    ORDER BY event_timestamp_kst
  ) AS prev_ts
FROM ga;

2) 오프셋/기본값 사용

-- 2행 이전 값을 가져오고, 없으면 'N/A' 반환
SELECT
  id,
  LAG(status, 2, 'N/A') OVER (ORDER BY created_at) AS status_2_rows_before
FROM t;

 

자주 하는 실수/주의점

• ORDER BY 누락: “이전”의 기준이 없으면 DB가 임의 순서로 판단할 수 있어 결과가 흔들림
• 동일 정렬값(동률): ORDER BY ts만으로는 동률 시 순서가 모호함. ORDER BY ts, id처럼 타이브레이커 가능
• NULL 처리: 첫 행(이전 행 없음)은 기본값이 NULL. 비교/연산 전 COALESCE로 기본값을 지정하거나 CASE로 분기하기
• 오프셋 범위 초과: 요청한 오프셋만큼 이전 행이 없으면 default_expr 또는 NULL을 반환합니다.

LEAD와의 차이

• LAG = 이전 행, LEAD = 다음 행을 가져옴. 작동 방식은 동일

윈도우 함수에 대한 설명이나 다른 윈도우 함수 예시를 보려면 아래 글을 참고하자.

SQL 코딩테스트 문제 풀이(3) - 레스토랑 요일 별 구매금액 Top 3 영수증(solvesql, RANK(), DENSE_RANK(), UNIO

 

3) julianday 사용하여 직전과 60분 이상 차이나는 경우를 세션 시작으로 표시

WITH f AS (  -- 대상 사용자 필터
  SELECT
    user_pseudo_id,
    event_timestamp_kst
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
o AS (  -- 이전 이벤트 시각 구하기
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    LAG(event_timestamp_kst) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp_kst
    ) AS prev_ts
  FROM f
),
-------------------추가된 부분-----------------------
m AS (  -- 60분 이상 비활동이면 세션 시작 플래그
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    CASE
      WHEN prev_ts IS NULL THEN 1
      WHEN (julianday(event_timestamp_kst) - julianday(prev_ts)) * 24 * 60 >= 60 THEN 1
      ELSE 0
    END AS is_new_session
  FROM o
)

 

추가된 부분의 괄호 안 쿼리만 실행하면 다음 결과가 나타난다.

실행 결과

 

 

CASE WHEN을 사용해서 세션 시작인 경우 1을 표시하도록 새로운 열(is_new_session)을 추가했다.

WHEN prev_ts IS NULL THEN 1을 통해 첫번째 행(이전 행이 없기 때문에 NULL 값)을 세션 시작으로 표시하였다.

WHEN (julianday(event_timestamp_kst) - julianday(prev_ts)) * 24 * 60 >= 60 THEN 1을 통해 현재 행과 직전 행의 로그 기록 시각의 차이를 구하고, 60분 이상이면 세션 시작으로 표시한다. event_timestamp_kst는 현재 행의 시각, prev_ts는 이전 단계에서 구한 직전 행의 시각이다.

 

이때 julianday를 사용하는데, 이는 특정 시각을 실수 형태로 변환하는 방법이다.

예를 들어 2022-01-24 12:34:21을 julianday를 사용하여 변환하면, 2459604.02385417이 된다. (이는 특정한 시점으로부터 2459604.02385417일 지났다는 뜻이다.)

시간을 실수로 변환하여 시간끼리 차이를 계산하거나, 연산이 가능하게 한다.

계산 결과에 24와 60을 곱하여 몇 분이 차이 나는지 구할 수 있다.

julianday 추가 설명

개념

• julianday(X [, modifier ...]) 는 인자 X(날짜·시간)를 줄리안 일수(Julian day number) 로 변환해 실수(REAL) 로 반환
• 값은 기원전 4714-11-24 12:00 UTC(줄리안 데이 원점)부터 지난 일 수(소수 포함)
• 서로 다른 시각의 차이를 일·시간·분으로 쉽게 환산할 때 자주 사용

줄리안 데이 숫자의 구조

• julianday() 결과 = 정수 부분 + 소수 부분
• 정수 부분 → 기원전 4714년 11월 24일(UTC) 정오로부터 경과한 날 수
• 소수 부분 → 하루 중에서 지난 시간 비율
  • 예: 0.5 = 하루의 절반 = 12시간
  • 예: 0.25 = 하루의 1/4 = 6시간

시각	반환값 (예시)	해석
2025-08-16 00:00	2461003.5	.5 → 자정 (줄리안 데이 기준이 정오라서 자정은 절반 시점)
2025-08-16 06:00	2461003.75	.75 → 자정 이후 6시간(= 하루의 1/4) 경과
2025-08-16 12:00	2461004.0	.0 → 정오(다음 날 정오가 다음 정수)

차이 계산 결과를 시간·분으로 바꾸는 법

하루 = 1.0

• 시간: 결과 * 24
• 분: 결과 * 24 * 60
• 초: 결과 * 24 * 60 * 60

 

4) SUM 윈도우 함수로 누적합을 구해 세선 번호 부여

WITH f AS (  -- 대상 사용자 필터
  SELECT
    user_pseudo_id,
    event_timestamp_kst
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
o AS (  -- 이전 이벤트 시각 구하기
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    LAG(event_timestamp_kst) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp_kst
    ) AS prev_ts
  FROM f
),
m AS (  -- 60분 이상 비활동이면 세션 시작 플래그
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    CASE
      WHEN prev_ts IS NULL THEN 1
      WHEN (julianday(event_timestamp_kst) - julianday(prev_ts)) * 24 * 60 >= 60 THEN 1
      ELSE 0
    END AS is_new_session
  FROM o
),
---------------------추가된 부분--------------------------
s AS (  -- 누적 합으로 세션 번호 부여
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    SUM(is_new_session) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp_kst
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_num
  FROM m
)

추가된 부분의 괄호 안 쿼리만 실행하면 다음 결과가 나타난다.

실행 결과

 

is_new_session 컬럼에 세션의 시작을 1로, 나머지는 0으로 표시했었다.

해당 컬럼을 누적 합하면, 첫 번째 세션의 경우 모두 1로, 두 번째 세션의 경우 세션 시작할 때 1이 더해지기 때문에 모두 2로 표시된다. 이를 통해 세션 번호를 매길 수 있다. 세션 번호를 매겨 놓으면 세션별 시작과 끝을 쉽게 찾을 수 있다.

PARTITION BY user_pseudo_id	유저 id에 따라 그룹으로 분류한 뒤 (여기선 하나의 id에 대한 것이므로 전체)
ORDER BY event_timestamp_kst	시간 순서대로(오름차순) 정렬한 뒤
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	누적 범위 지정: 파티션 그룹 내에서 처음 행(UNBOUNDED PRECEDING) 부터 현재 행(CURRENT ROW)까지
SUM(is_new_session)	합을 구하라

 

(이해를 돕기 위한 예시)

 

5) GROUP BY를 통해 세션별 시작 시간과 종료 시간 찾기

최종 모범 답안

WITH f AS (  -- 대상 사용자 필터
  SELECT
    user_pseudo_id,
    event_timestamp_kst
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
o AS (  -- 이전 이벤트 시각 구하기
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    LAG(event_timestamp_kst) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp_kst
    ) AS prev_ts
  FROM f
),
m AS (  -- 60분 이상 비활동이면 세션 시작 플래그
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    CASE
      WHEN prev_ts IS NULL THEN 1
      WHEN (julianday(event_timestamp_kst) - julianday(prev_ts)) * 24 * 60 >= 60 THEN 1
      ELSE 0
    END AS is_new_session
  FROM o
),
s AS (  -- 누적 합으로 세션 번호 부여
  SELECT
    user_pseudo_id,
    event_timestamp_kst,
    SUM(is_new_session) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp_kst
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_num
  FROM m
)
---------------------추가된 부분----------------------
SELECT
  user_pseudo_id,
  MIN(event_timestamp_kst) AS session_start,
  MAX(event_timestamp_kst) AS session_end
FROM s
GROUP BY user_pseudo_id, session_num
ORDER BY session_start;

CTE 밑의 메인 쿼리를 실행하면 최종 결과가 나타난다.

실행 결과

 

유저 ID(user_pseudo_id, 어차피 한 유저에 대한 것이라 사실 GROUP BY에 쓰지 않아도 된다)와 세션 번호(session_num)를 기준으로 그룹화를 한 뒤 해당 세션 내의 가장 빠른 시간(세션 시작)을 MIN 집계함수로, 세션 내의 가장 늦은 시간(세션 끝)을 MAX 집계함수로 구하면 최종 결과가 나타난다. 이전 단계에서 세션 번호를 매긴 이유는 세션을 기준으로 집계 함수를 사용하기 위해서이다.

 

이렇게 총 다섯 단계에 걸쳐서 답을 구할 수 있다. 꽤나 복잡하지만 차근차근 단계적으로 처리를 하면 충분히 해결 가능하다.

 

3. 풀이 2 - ROW_NUMBER 윈도우 함수 사용, 혼자 풀기

처음 혼자서 문제를 풀었을 땐 LAG가 아니라 셀프 JOIN을 활용해서 시간 차이를 구했고, 누적합을 통해 세션 번호를 구할 생각을 못했기 때문에 아주 어지러운(?) 쿼리가 나왔다.

with TABLE_A AS( ---유저의 로그만 추출, 셀프 JOIN을 위한 행번호 삽입
  select *,
    ROW_NUMBER() OVER (ORDER BY event_timestamp_kst) as id
  FROM ga
  where user_pseudo_id='S3WDQCqLpK'
),

TABLE_B AS( --- 행번호에 +/- 1을 한 뒤 셀프 JOIN하여 직전, 직후와 시간 차이를 구함
select A.*,
  CASE WHEN STRFTIME('%s',A.event_timestamp_kst)-STRFTIME('%s',B.event_timestamp_kst)>=3600
    THEN 1 --- 직전과 시간 차이가 60분 이상일 때 세션 시작
  WHEN A.id=1
    THEN 1 --- 첫번째 행은 세션 시작
  WHEN STRFTIME('%s',C.event_timestamp_kst)-STRFTIME('%s',A.event_timestamp_kst)>=3600
    THEN 2 --- 직후와 시간 차이가 60분 이상일 때 세션 끝
  WHEN A.id=(select max(id) from TABLE_A)
    THEN 2 --- 마지막 행은 세션 끝
  END AS session
from TABLE_A as A 
left join TABLE_A as B
on A.id=B.id+1 --- 직전과 시간 차이 구하기 위해 셀프 JOIN
left join TABLE_A as C
on A.id=C.id-1), --- 직후와 시간차이 구하기 위해 셀프 JOIN

TABLE_C AS( --- 세션 시작(1)일 때 session_start 열에 시간 표시
select user_pseudo_id,
  CASE WHEN session=1 THEN event_timestamp_kst END as session_start
FROM TABLE_B),

TABLE_D AS( --- 세션 끝(2)일 때 session_end 열에 시간 표시
SELECT user_pseudo_id,
  CASE WHEN session=2 THEN event_timestamp_kst END as session_end
from TABLE_B)

--- 세션 시작 테이블과 세션 끝 테이블을 null값 제거한 뒤 행번호 기준으로 JOIN
SELECT start.user_pseudo_id,session_start,session_end
from (select *, ROW_NUMBER() OVER (ORDER BY session_start) as id
  from TABLE_C
  WHERE session_start is NOT NULL) as start
join (select *, Row_number() OVER (ORDER BY session_end) as id
  from TABLE_D
  WHERE session_end is NOT NULL) as end
ON start.id = end.id

 

복잡하고, join을 위해 불필요한 행 번호를 두 번이나 삽입하는 등 좋은 쿼리는 아니다. 하지만 윈도우 함수를 잘 모를 때는 셀프 조인을 사용하여 직전이나 직후의 값을 가져와 계산하는 것도 괜찮은 방법이다.

 

left join TABLE_A as B
on A.id=B.id+1 --- 직전과 시간 차이 구하기 위해 셀프 JOIN

해당 부분에서 기존 테이블에다가 행 번호에 1을 더한 테이블을 JOIN한다. 행 번호에 1을 더한 값은 "현재 행 기준으로" 직전 행에 해당한다. 직전 행이지만 +1을 하기 때문에 헛갈리지 않도록 조심.

 

행 번호를 삽입하는 것은 ROW_NUMBER 윈도우 함수를 사용하면 된다.

 

해당 쿼리에서는 시간 계산을 할 때 julianday가 아니라 STRFTIME을 사용한다. STRFTIME은 실수를 반환하는 julianday와 달리 문자열로 값을 반환한다. 하지만 위 쿼리와 같이 %s를 사용하면 정수 형태의 문자열(숫자처럼 보이지만 자료형은 TEXT이다)을 구할 수 있고, 연산이 가능(정수로 변환 후 연산해야 하지만, SQLite에서는 숫자로 변환 가능한 문자열일 때는 자동으로 변환해서 연산을 함)하다. julianday와 달리 부동소수점에서 자유롭다.

 

4. SQL 시간 변수 다루기 - JULIANDAY와 STRFTIME

1. julianday()

• 용도: 날짜/시간을 하나의 실수 값(줄리안 일수) 으로 변환
• 반환값: REAL (소수 포함된 숫자)
• 특징:
  • 기준: 기원전 4714년 11월 24일 12:00 UTC (Julian Day 0)
  • 소수점 = 하루 중 몇 %가 지났는지
    (예: 2461003.75 → 0.75일 = 18시간)
  • 날짜 간 차이 계산할 때 매우 유용
• 예시:

SELECT julianday('2025-08-16 12:00:00'); -- 결과: 2461004.0
SELECT (julianday('2025-08-16 12:00:00') - julianday('2025-08-15 12:00:00')) * 24;
-- 결과: 24.0 (두 날짜 사이 24시간)

2. strftime()

• 용도: 날짜/시간을 지정한 형식의 문자열로 변환
• 반환값: TEXT (문자열)
• 특징:
  • C언어의 strftime() 형식을 따름
  • 원하는 형태로 포맷 출력 가능
  • %s 포맷을 쓰면 UNIX 타임스탬프(초) 반환 → 정수로 활용 가능
• 예시:

SELECT strftime('%Y-%m-%d %H:%M', '2025-08-16 12:34:56') -- 결과: '2025-08-16 12:34'
SELECT strftime('%s', '2025-08-16 12:00:00'); -- 결과: 1755307200 (UNIX timestamp, 초 단위)

3. 비교 정리

구분	julianday()	strftime()
반환값	REAL (실수, 줄리안 일수)	TEXT (문자열, 원하는 형식) / %s → 초 단위 정수
주요 목적	날짜 간 차이 계산	날짜/시간 포맷 출력, 특정 단위 추출
예시 결과	2461003.75 (2025-08-16 18:00)	'2025-08-16 18:00:00'
차이 구할 때	julianday('2025-08-16') - julianday('2025-08-15')	CAST(strftime('%s','2025-08-16') AS INT) - ...
장점	계산에 직관적 (일 단위 → 시간 환산)	가공·출력에 직관적 (연도, 월, 요일 등 추출)
단점	실수 오차 가능	계산하려면 CAST 필요(SQLite는 자동 CAST), 포맷 지정 번거로움

 

시간 데이터에 대해 더 알고 싶다면 이전 글을 참고하자.

[SQL로 시작하는 데이터 분석] (3) - 시계열 분석

[SQL로 시작하는 데이터 분석] (3) - 시계열 분석 2

 

마무리

오늘은 네 번째 SQL 코딩테스트 문제 풀이를 해보았다. solvesql의 '세션 재정의하기' 문제였으며 CTE, LAG, ROW_NUMBER, JULIANDAY, 누적합, STRFTIME, 윈도우 함수에 대해 더 자세히 공부하는 계기가 되었다. 다음엔 어떤 문제를 풀어볼까? 두근두근~