Teja Teja - 1 month ago 10
SQL Question

Get new users compared to yesterday

I am having a user traffic table where I need to get the gain/loss of new users compared to the previous day. Just wondering if there is a better way to do this rather than the below solution.

Schema :-

Table Strcutre: Session_ID, session_day, user_id, product_id


What I have tried?

SELECT session_day,
session_count,
user_count - LAG( user_count, 1 ) OVER ( ORDER BY session_day ) AS gain_loss_users
FROM
(
SELECT session_day,
COUNT( session_id ) AS session_count,
COUNT( user_id ) user_count
FROM user_traffic
GROUP BY session_day
) X ;

Answer

I tried to resolve "new" and "returning" persons problem. Here is my attempt:

    select session_day, 
       COUNT( distinct user_id ) AS user_cnt,
       count(distinct user_id) - lag(count(distinct user_id)) 
                                     over (order by session_day) gain,
       count(newu) AS  newu, count(returnu) AS returnu
  from (
          select session_id,
                 session_day,
                 user_id, 
                 CASE WHEN
                 count(*) over ( partition by user_id ORDER BY session_day,session_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
                           = 1 
                      THEN 1
                  END 
                  AS newu,
                 CASE WHEN 
                 lag( session_day,1 ) over ( partition by user_id ORDER BY session_day,session_id ) 
                           <> 
                           lag( session_day,1 ) over ( order by session_day,session_id ) 
                      THEN 1
                 END  AS returnu    
            from user_traffic u
        )
  group by session_day
  order by session_day;

Test data and output:

create table user_traffic (session_id number(6), session_day date, 
                           user_id number(6), product_id number(6));

insert into user_traffic values (  1, date '2016-09-07', 101, 1);
insert into user_traffic values (  2, date '2016-09-07', 101, 4);
insert into user_traffic values (  3, date '2016-09-07', 102, 1);
insert into user_traffic values (  4, date '2016-09-08', 101, 2);
insert into user_traffic values (  5, date '2016-09-08', 101, 4);
insert into user_traffic values (  6, date '2016-09-09', 102, 1);
insert into user_traffic values (  7, date '2016-09-10', 102, 1);
insert into user_traffic values (  8, date '2016-09-10', 103, 3);

SESSION_DAY        CNT       GAIN        NEW    RETURNS
----------- ---------- ---------- ---------- ----------
2016-09-07           2                     2          0   -- 101 & 102 are new
2016-09-08           1         -1          0          0
2016-09-09           1          0          0          1   -- 102 returned
2016-09-10           2          1          1          0   -- 103 is new
Comments