Question 287086 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get new user vs returning visitor list using SQL query

I want to get the list of users who are new users vs returning user.
Report is taken for every week. If user logs in for the first time in a week he is considered as new user,in a week if user logs in multiple times he is still new user.
If user has logged in previous week and re logged in this week then he becomes returning user.

I have table with:
user_name, login_date, action [as login or logout] enter image description here

I have query that is currently giving me response as:
enter image description here

WITH first_visit AS (
         SELECT v.user_name,
            min(v.login_date) AS first_login_date
           FROM db.user v
          GROUP BY v.user_name
        ), visitors_list AS (
         SELECT u.doc_id,
            u.user_name,
            u.login_date
                CASE
                    WHEN fv.first_login_date > (CURRENT_DATE - '7 days'::interval) THEN 'New user'::text
                    ELSE 'Returning User'::text
                END AS user_status
           FROM db.user u
             JOIN first_visit fv ON u.user_name::text = fv.user_name::text
          WHERE u.login_date < CURRENT_DATE AND u.login_date > (CURRENT_DATE - 7) AND u.action::text = 'Login'::text
        )
 SELECT v.doc_id, v.user_name,
    v.user_status,
    v.first_login_date
   FROM visitors_list v
  GROUP BY v.user_name, v.user_status, v.first_login_date ,v.doc_id ;

Here duplicate data is coming for same user , I need help with understanding how we can get single data for each user along with doc_id and login_Date , if I remove the doc_id I do get unique results but I want doc_id also.

Tags:

2

Answers


  1. 0

    I want to get the list of users who are new users vs returning user. Report is taken for every week.

    Schematically:

    SELECT user,
       EXISTS ( SELECT NULL
                FROM users u2
                WHERE u1.user = u2.user
                  AND u2.date < CURRENT_DATE - INTERVAL 1 WEEK
             --   AND u2.date < u1.date - INTERVAL 1 WEEK
               ) AS is_returned_user
FROM users u1
WHERE date >= CURRENT_DATE - INTERVAL 1 WEEK

    For each user who have logged in within last week the query tests does the log in row out of current week exists. If it exists then is_returned_user returns TRUE otherwise it returns FALSE.

    Login or Signup to reply.
  2. 0

    Added the ranks for visits in your CTE and selected recent visits to get the desired output. Code is as follows:

    WITH first_visit AS (
     SELECT v.user_name,
            min(v.login_date) AS first_login_date
       FROM user v
      GROUP BY v.user_name
    ), visitors_list AS (
     SELECT u.doc_id,
            u.user_name,
            u.login_date,
      -- added a rank every visit
            dense_rank() OVER (partition by u.user_name order by u.login_date desc) user_rank,
            CASE
                WHEN fv.first_login_date > (CURRENT_DATE - INTERVAL 7 DAY) THEN 'New user'
                ELSE 'Returning User'
            END AS user_status
       FROM user u
      INNER JOIN first_visit fv ON u.user_name = fv.user_name
      WHERE u.login_date <= CURRENT_DATE AND u.login_date > (CURRENT_DATE - 7) AND u.action = 'Login'
    )
 SELECT v.doc_id,
        v.user_name,
        v.user_status,
        v.login_date
   --  ,v.user_rank
   FROM visitors_list v
  WHERE v.user_rank = 1 -- filter recent visits only
  -- GROUP BY v.user_name, v.user_status, v.login_date  ,v.doc_id 
  ;

    DB fiddle for reference returning user sql

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search