문제 1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요 WITH base AS ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week, DATE_TRUNC(MIN(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week FROM `advanced.app_logs` ), first_week_diff AS ( SELECT user_pseudo_id, DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week FROM base ), user_counts AS ( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_count FROM first_week_diff GROUP BY diff_of_week ) SELECT diff_of_week, user_count, ROUND(SAFE_DIVIDE(user_count, FIRST_VALUE(user_count) OVER (ORDER BY diff_of_week ASC)), 2) AS retention_rate FROM user_counts ORDER BY diff_of_week; 문제 2) Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해보세요. WITH event_data AS ( SELECT DISTINCT DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, user_pseudo_id FROM advanced.app_logs ), user_visits AS ( SELECT user_pseudo_id, MIN(event_date) AS first_visit, MAX(event_date) AS last_visit -- 최근 방믄 FROM event_data GROUP BY user_pseudo_id ), -- 최신일자 current_date AS ( SELECT MAX(event_date) AS max_date FROM event_data ), user_types AS ( SELECT u.user_pseudo_id, u.first_visit, u.last_visit, c.max_date as current_date, DATE_DIFF(c.max_date, u.last_visit, DAY) as days_since_last_visit, CASE WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) <= 14 THEN 'current' -- 최근 2주 내 방문 WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) > 60 THEN 'dormant' -- 60일 이상 미접속 WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) > 30 AND DATE_DIFF(c.max_date, u.last_visit, DAY) <= 44 AND EXISTS ( SELECT 1 FROM event_data e WHERE e.user_pseudo_id = u.user_pseudo_id AND DATE_DIFF(c.max_date, e.event_date, DAY) <= 14 ) THEN 'resurrected' -- 30일 이상 비활동 후 최근 14일 내 재접속 WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) > 60 THEN 'inactive' -- 60일 이상 비활동 ELSE 'none' END AS user_type FROM user_visits u CROSS JOIN current_date c ), weekly_retention AS ( SELECT ut.user_type, DATE_DIFF(DATE_TRUNC(ed.event_date, WEEK), DATE_TRUNC(ut.first_visit, WEEK), WEEK) as week_number, COUNT(DISTINCT ed.user_pseudo_id) as user_count FROM event_data ed JOIN user_types ut ON ed.user_pseudo_id = ut.user_pseudo_id GROUP BY 1, 2 ), retention_rates AS ( SELECT user_type, week_number, user_count, FIRST_VALUE(user_count) OVER ( PARTITION BY user_type ORDER BY week_number ) as initial_users FROM weekly_retention ) SELECT user_type, week_number, user_count as active_users, initial_users as cohort_size, ROUND(user_count * 100.0 / initial_users, 2) as retention_rate FROM retention_rates ORDER BY user_type, week_number; 문제 3) 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? 찾아보세요 - current의 경우 15주까지는 리텐션 유지 및 증가하며 큰 변동 없다가 그 후부터 지속적으로 하락하였다. - current는 그래도 리텐션율이 9.X로 시작했으나 dormant는 7.X로 시작하였고 더 빠르게 유입율이 감소하며 리텐션이 좋지 않았다. 문제 4) Core Event를 “click_payment”라고 설정하고 Weekly Retention을 구해주세요 WITH base AS ( SELECT user_pseudo_id, DATE_TRUNC(DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')), WEEK(MONDAY)) as week FROM advanced.app_logs WHERE event_name = "click_payment" ), cal_week AS ( SELECT user_pseudo_id, week as visit_week, MIN(week) OVER (PARTITION BY user_pseudo_id) as first_week FROM base ), weekly_retention AS ( SELECT first_week DATE_DIFF(visit_week, first_week, WEEK) as week_number, COUNT(DISTINCT user_pseudo_id) as user_count FROM cal_week GROUP BY first_week, DATE_DIFF(visit_week, first_week, WEEK) ), cohort_sizes AS ( SELECT first_week, MAX(CASE WHEN week_number = 0 THEN user_count END) as initial_users FROM weekly_retention GROUP BY cohort_week ) SELECT wr.first_week, cs.initial_users as total_users, wr.week_number, wr.user_count as active_users, ROUND(wr.user_count * 100.0 / cs.initial_users, 2) as retention_rate FROM weekly_retention wr JOIN cohort_sizes cs ON wr.cohort_week = cs.cohort_week WHERE wr.week_number >= 0 ORDER BY wr.cohort_week, wr.week_number;
학습하는 분들께 도움이 되고, 더 좋은 답변을 드릴 수 있도록 질문전에 다음을 꼭 확인해주세요. 1. 강의 내용과 관련된 질문을 남겨주세요. 2. 인프런의 질문 게시판과 자주 하는 질문(링크)을 먼저 확인해주세요. (자주 하는 질문 링크: https://bit.ly/3fX6ygx) 3. 질문 잘하기 메뉴얼(링크)을 먼저 읽어주세요. (질문 잘하기 메뉴얼 링크: https://bit.ly/2UfeqCG) 질문 시에는 위 내용은 삭제하고 다음 내용을 남겨주세요. ========================================= [질문 템플릿] 1. 강의 내용과 관련된 질문인가요? (예/아니오) 2. 인프런의 질문 게시판과 자주 하는 질문에 없는 내용인가요? (예/아니오) 3. 질문 잘하기 메뉴얼을 읽어보셨나요? (예/아니오) [질문 내용] OCP를 공부하면서 궁금한점이 있습니다. Car 인터페이스를 implements 하는 GoodCar이라는 클래스에 Nav() 라는 네비게이션 기능을 추가하려면 Car 인터페이스에도 Nav()라는 함수를 추가해야 Driver가 사용가능할거 같습니다. 이러면 기존의 Car을 implements하는 클래스들이 매서드 미구현으로 에러가 발생합니다. 그래서 Car 인터페이스를 안바꾸려면 앞서 배운 fly 인터페이스처럼 따로 인터페이스를 만들면 구현은 가능할거 같은데, 이런 경우 OCP를 지킨다고 할 수 있나요? 아니면 다른 방법이 있을까요?
안녕하세요. 이번에 JPA 로드맵을 다시 한번 복습을 하면서 부모 객체, 자식 객체에 대한 궁금한 점이 생겼습니다. 부모 객체, 자식 객체 관계의 용어는 어떤 상황에서 사용해야 하는 건가요? 연관 관계도 아닌 것 같고.. 상속 관계도 아닌 것 같고.. (자바, 스프링 로드맵에서 강사님이 항상 강조하시는 말씀이 있죠. 부모는 ~ 품을 수 있지만 자식은 ~ 품을 수 없다. 근데 JPA에서는 전혀 다른 상황인 것 같아서요.) cascade와 orphanRemoval 기능을 사용하기 위해 cascade와 orphanRemoval 코드를 내부에 적은 객체 자체가 부모 객체가 되는 건가요? 예를 들면 @Entity @Table(name = "orders") @Getter @Setter @NoArgsConstructor(access = AccessLevel.PROTECTED) public class Order { @Id @GeneratedValue @Column(name = "order_id") private Long id; @ManyToOne(fetch = LAZY) @JoinColumn(name = "member_id") private Member member; @JsonIgnore @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true) private List<OrderItem> orderItems = new ArrayList<>(); @JsonIgnore @OneToOne(fetch = LAZY, cascade = CascadeType.ALL, orphanRemoval = true) @JoinColumn(name = "delivery_id") private Delivery delivery; ... 위의 코드에서 cascade = CascadeType.ALL, orphanRemoval = true 기능을 사용해서 생명주기의 책임이 있는 Order 엔티티 객체가 부모 객체가 되고 OrderItem과 Delivery 엔티티 객체가 자식 객체가 되는건가요?
1번 with base as ( select user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) as event_first_week, DATE_TRUNC(event_date,WEEK(monday)) as event_week, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_time from advanced.app_logs ) , diff_date_tbl as ( select user_pseudo_id, event_first_week, event_week, DATE_DIFF(event_week, event_first_week, WEEK) as diff_date from base order by diff_date ) , user_counts as ( select event_first_week, diff_date, count(distinct user_pseudo_id) as user_count from diff_date_tbl group by all order by diff_date ) select * , ROUND(SAFE_DIVIDE(user_count, first_user_count), 2) as retention_rate from ( select * , FIRST_VALUE(user_count) OVER (PARTITION BY event_first_week ORDER BY diff_date) as first_user_count from user_counts ) 2번 with base as ( select user_pseudo_id, MIN(event_date) OVER (PARTITION BY user_pseudo_id) as event_first_day, event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_time from advanced.app_logs ) , period as ( select user_pseudo_id, event_date, LAG(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) as before_event_date from base GROUP BY ALL ORDER BY user_pseudo_id ) -- 평균주기 구하기 SELECT AVG(diff_day) as avg_diff_period FROM ( select *, DATE_DIFF(event_date, before_event_date, DAY ) as diff_day from period ) -- 접속 주기 분포도 확인 SELECT diff_day, COUNT(distinct user_pseudo_id) as user_count FROM ( select *, DATE_DIFF(event_date, before_event_date, DAY ) as diff_day from period ) GROUP BY ALL ORDER BY diff_day 1) new 유저 = (최근월 - 1)월 = 첫접속일자(event_first_month) 2) current 유저 = (최근월 -1)월 , (최근월 -2)월 모두 활동 o 3) dormant 유저 = (최근월 - 1)월 , (최근월 -2)월 모두 활동 x 4) resurrected 유저 = (최근월 -2)월 활동 x, (최근월 -1)월 에는 활동 with base as ( select user_pseudo_id, event_date, DATE_TRUNC(event_date, MONTH) as event_month from advanced.app_logs order by 1 ) , min_max_month as ( SELECT *, MIN(event_month) over (partition by user_pseudo_id order by event_month) as first_month, MAX(DATE_TRUNC(event_date, MONTH)) OVER () as lastest_month from base ) , filter_month as ( SELECT user_pseudo_id, first_month, lastest_month, COUNT(CASE WHEN event_month = DATE_SUB(lastest_month, INTERVAL 1 MONTH) THEN 1 END) AS previous_1_lastest_month, COUNT(CASE WHEN event_month = DATE_SUB(lastest_month, INTERVAL 2 MONTH) THEN 1 END) AS previous_2_lastest_month FROM min_max_month group by ALL ) , user_classification as ( SELECT *, CASE WHEN first_month = DATE_SUB(lastest_month, INTERVAL 1 MONTH ) THEN 'New' WHEN previous_1_lastest_month > 0 and previous_2_lastest_month >= 0 THEN 'Current' WHEN previous_1_lastest_month = 0 and previous_2_lastest_month = 0 THEN 'Dormant' WHEN previous_1_lastest_month = 0 and previous_2_lastest_month > 0 THEN 'Resurrected' ELSE 'Others' END AS user_category FROM filter_month ) select user_category, count(distinct user_pseudo_id) from user_classification group by all 4번 WITH base AS ( SELECT user_pseudo_id , first_value(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) over(partition by user_pseudo_id order by event_timestamp) as first_visit , last_value(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) over(partition by user_pseudo_id order by event_timestamp) as last_visit , datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime , date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), day) as event_date , date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), week(monday)) as event_week , date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), month) as event_month , event_name FROM advanced.app_logs ), click_payment AS ( SELECT DISTINCT user_pseudo_id , min(event_week) over(partition by user_pseudo_id) as first_week , event_week as week_date FROM base WHERE event_name = 'click_payment' ), diff_week AS ( SELECT first_week, date_diff(week_date, first_week, week) as week_diff, count(distinct user_pseudo_id) as users_cnt FROM click_payment GROUP BY ALL ) SELECT first_week , week_diff , users_cnt , safe_divide(users_cnt, first_value(users_cnt) over(partition by first_week order by week_diff)) as retention_rate FROM diff_week order by 1, 2 ;
@ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "user_id") private User createUser; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "user_id") private User lastModifyUser; DB 한 테이블에 글 등록 유저 정보와 마지막 수정 유저 정보를 넣도록 설계했는데 아래와 같이 안되면 이럴때는 보통 설계를 어떻게 하나요?? Caused by: org.hibernate.MappingException : Column 'user_id' is duplicated in mapping for entity 'study.factory.domain.Order' (use '@Column(insertable=false, updatable=false)' when mapping multiple properties to the same column)
