안녕하세요~
고수님들의 도움을 구하고자 처음 문의글을 남깁니다!
월별로 생애 2번째 구매를 달성한 유저들의 3번째 구매까지의 구매주기를 구하고 싶은데요.
ex) 7월에 생애 2번째 구매를 한 유저는 1만명인데 3번째 구매까지의 평균 구매주기는 14일이다.
LAG라는 함수를 이용하여 리드타임을 구할 수 있다는 것까지는 어떻게 발견했는데,,
특정 기간 안에 생애 2번째 구매를 달성한 유저를 어떻게 추출해야 할지, 그리고 그 유저의 3번째 구매는 어떻게 구할 수 있을지 고수님들의 의견을 구합니다ㅠ
제 막연한 생각으론 유저별 max timestamp를 가져오고, 구매 rank가 3번째가 포함된 유저 id만 추출해서 두 개가 매칭되는 id를 추출한 뒤 LAG 함수를 활용해 2번째 구매고객의 3번째 구매까지의 리드타임을 구할 수 있지 않을까 생각했는데 가능한 건지 모르겠네요..!
----------------------------------------------------------------
당연하겠지만 1번째 구매한 유저들만 뽑아서 구매주기를 보려고 하니까 주기가 안나오더라고요 ㅠ
부끄럽지만 이것저것 적어보던 쿼리를 첨부합니다!
WITH data AS (
SELECT
user_no
, MIN(pay_timestamp) as pay_timestamp
FROM mart.order
GROUP BY 1
)
select
user_no
,pay_timestamp
,date_diff(date(timestamp(pay_timestamp)), LAG(date(timestamp(pay_timestamp))) over(partition by user_no order by pay_timestamp), day) as lead_time
from data
where pay_timestamp BETWEEN '{{ date_range.start }}' AND '{{ date_range.end }}'
Comment 4
-
지영아빠
2022.08.17 16:15
-
Kr8s
2022.08.22 14:08
도움주셔서 정말 감사합니다 지영아빠님!!ㅠㅠ
다만, 작성해주신 쿼리를 제가 이해하지 못한 부분이 많아서 수정을 하고 있는데요!
Unrecognized name: Second_NTH_VALUE at [15:7]라는 오류가 나와서 중단한 상태입니다.
NTH_VALUE라는 함수 사용법 그대로 작성했는데 왜 오류가 난건지 잘 모르겠습니다...
혹시 해결할 수 있는 추가 의견 주실 수 있을까요?
WITH data AS (
SELECT
user_no
,pay_timestamp
, DENSE_RANK() OVER (partition by user_no order by pay_timestamp) AS dense_rank
from mart.order
)select
user_no
,NTH_VALUE(data.pay_timestamp, 2) over(PARTITION BY user_no ORDER BY data.dense_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Second_NTH_VALUE
,NTH_VALUE(data.pay_timestamp, 3) over(PARTITION BY user_no ORDER BY data.dense_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Third_NTH_VALUE
,date_diff(day, date(Third_NTH_VALUE), date(Second_NTH_VALUE)) as lead_time
from data
where Second_NTH_VALUE BETWEEN '{{ date_range.start }}' AND '{{ date_range.end }}'
; -
지영아빠
2022.08.23 13:54
nth_value는 회사에서 만든 함수인듯한데요 (잘 모르는 함수예요)
nth_value() over(....) over이하 부분도 문법에 맞지 않는 듯합니다.
-
Kr8s
2022.08.23 15:19
nth_value 함수는 구글 검색으로 찾았는데 잘 사용하지 않는 함수인가보네요! 도움주셔서 감사합니다~!
참고하십시오.
with tmp as (
select '1' as user_no, cast('2022-07-01' as datetime) as dt union all
select '1' as user_no, '2022-08-01' as dt union all
select '1' as user_no, '2022-10-01' as dt union all
select '2' as user_no, '2022-07-01' as dt union all
select '3' as user_no, '2022-07-01' as dt union all
select '3' as user_no, '2022-11-01' as dt union all
select '3' as user_no, '2022-12-01' as dt
)
select
a.user_no,
b.dt as prev_pay,
a.dt as lag_pay,
isnull(datediff(d, b.dt, a.dt), 0) as pay_interval, -- 이전 구매와의 차이를 구한 날짜
datediff(d, c.dt, a.dt ) as pay_agg_interval -- 최초 구매에서 누적된 날짜
from (
select
user_no, dt, rank() over (partition by user_no order by dt) as n
from tmp
) a
left outer join (
select
user_no, dt, rank() over (partition by user_no order by dt) as n
from tmp
) b
on a.user_no = b.user_no
and a.n = b.n + 1
CROSS APPLY (SELECT min(dt) as dt FROM tmp WHERE user_no = a.user_no) c