일정 기간 후 복귀한 사용자 가져오기

커뮤니티 구축 데이터 및 보고
1

커뮤니티 여러분 안녕하세요,

6개월(마지막 접속 시각: last_seen_at) 동안 커뮤니티를 떠났다가 돌아온 사용자 목록을 가져오고 싶습니다.

users 테이블에서 last_seen_at 날짜를 가져올 수 있다는 것은 알고 있습니다. 이 타임스탬프를 비교하여 6개월 이상인지 확인하고, 지난 6개월 동안 방문하지 않은 사용자를 조회할 수 있습니다. 하지만 사용자가 커뮤니티에 한 번만 방문해도 이 타임스탬프가 현재 날짜와 시간으로 덮어쓰기 때문에 사용자를 추적하기가 어렵습니다.

마지막 방문 이후 일정 시간 간격이 지난 후 사용자가 포럼에 다시 돌아왔는지 확인하는 것이 가능한가요?

감사합니다!

6

필요한 세부 사항은 user_visits 테이블에 있습니다. 저에게 어려운 부분은 각 방문 항목에서 이전 방문 값을 빼는 것입니다. 윈도우 함수가 필요해 보입니다. StackExchange의 이 질문에 대한 답변을 바탕으로, 이 작업을 위해 LEAD 함수가 적합해 보입니다.

아래 쿼리를 실행해 보시고, 원하는 데이터가 반환되는지 확인해 보세요. 기본적으로 방문 간격이 180일 이상인 모든 사용자 방문을 반환하도록 설정되어 있습니다. 쿼리의 days_away 파라미터를 설정하여 이 값을 조정할 수 있습니다.

--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)

SELECT * FROM days_between_visits WHERE time_away >= :days_away
ORDER BY visited_at DESC

반환된 데이터에 문제가 있는지 알려주세요.

7

좋아요. OP의 요구사항을 충족할지는 모르겠지만, 나에게는 >x days면 충분합니다.

SQL을 할 줄 아는 모든 분들, 정말 감사합니다. 저에게는 그 모든 트릭을 배울 엄두가 안 나거든요 :squinting_face_with_tongue:

9

@simon 정말 감사합니다!

제 사용 사례에 완벽하게 작동합니다 :100:

정말 SQL 마법사시네요 :mage:

10

@simon님, 안녕하세요.

다시 한번 번거롭게 해 드려 죄송합니다. 위의 SQL 쿼리에서 user_id 대신 username을 가져올 수 있을까요? 데이터 탐색기 페이지에서는 쿼리 결과에 사용자 이름이 표시되지만, 결과를 CSV 파일로 내보내면 사용자 이름이 user_id로 대체됩니다. CSV 파일에도 사용자 이름이 표시되기를 선호합니다.

감사합니다.

11

물론이죠, 아래 코드를 시도해 보세요:

--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)

SELECT
username,
dbv.user_id AS id,
visited_at,
time_away
FROM days_between_visits dbv
JOIN users u ON u.id = dbv.user_id
WHERE time_away >= :days_away
ORDER BY visited_at DESC

실행 중 타임아웃 오류가 발생하면 알려 주세요. 반환되는 결과에 여전히 사용자의 ID가 포함되어 있습니다. 해당 열은 이제 id로 표시되어 있습니다. ID를 반환하지 않으려면 마지막 SELECT 문에서 dbv.user_id AS id, 줄을 삭제하세요.

12

다시 한번 감사드립니다!
타임아웃 오류가 발생하지 않았습니다 :slight_smile: