Customer Weekly logins analysis

Question

Maybe someone could help?

I'm using PostgreSQL, and I'm trying to create a Cohort analysis so I can track user logins. I’m not an SQL guru myself, but I’ve found many examples, but many of them customized to MySQL or outdated. I've been stuck at this point for a couple of days, thank you.

Cohort - customers who registered in one week (e.g. week 19)

Desired results:

Let me explain... We have one row which is Cohort, the first week is the count of registered users, the next week shows how many of the registered users logged in, and the third week is also showing how many of the users have logged in after the second week, and it keeps continuing.

2 tables are used: users_user, users_logins.

users_user fields: | id | last_login | date_joined |
users_logins fields: | created_at | user_id |

Required table:

Answer 1

SELECT DATE_PART('week', date_joined),
COUNT(
    CASE 
        WHEN (DATE_PART('day', last_login - date_joined) <= 1*7) THEN 1 
        ELSE NULL))
    END
) AS week_1,
COUNT(
    CASE 
        WHEN (DATE_PART('day', last_login - date_joined) <= 2*7) THEN 1 
        ELSE NULL))
    END
) AS week_2,
COUNT(
    CASE 
        WHEN (DATE_PART('day', last_login - date_joined) <= 3*7) THEN 1 
        ELSE NULL))
    END
) AS week_3,
COUNT(
    CASE 
        WHEN (DATE_PART('day', last_login - date_joined) <= 4*7) THEN 1 
        ELSE NULL))
    END
) AS week_4
from users_user
group by DATE_PART('week', date_joined) 

Notes:

• Expand for as many columns as you want.
• You probably also want to add a year column to the SELECT and the GROUP BY so it doesn't break for time spans greater than a year.
• I wrote this without a database, so it contains bugs and typos, but I hope the basic idea comes across.