How to identify client login or not for one month

I have a table with 3 columns: day of month, client_uk, flag_login (Y/N). My task is to group this table over by month to determine what flag_login should be for each month for every client. The client could be login a few time per month, for me, it doesn’t matter and I should determine whether the fact of client login at least 1 time per month. If it’s yes flag_login should Y, in opposite case ‘N’.

I don’t know how to solve it, because my window for window function should be client_uk and month simultaneously.

Thanks for the help in advance!

REPLIES

makaroni4

over 4 years Reply

That’s a really great question, I see such user engagement reports all the time

As I understand correctly, the client_uk is some sort of user/client unique identifier, day of month is the date and flag_login is a boolean that tells us whether user was logged in or not on a certain day:

day_of_month	client_uk	flag_login
2019-11-03	foo123	TRUE
2019-12-04	foo123	FALSE
2019-11-05	bar456	TRUE
2019-12-12	bar456	FALSE

The final result set should tell us whether a user/client logged in at least once per each month:

month	year	client_uk	logged_in_once
11	2019	foo123	TRUE
12	2019	foo123	FALSE
11	2019	bar456	TRUE
12	2019	bar456	FALSE

With such report we’re 1 step away from calculating MAU (Monthly Active Users), for example. Let’s see how can we get to such result set

I think the trick here is to use grouping and counting and then convert counts into booleans with a simple rule: 0 logins translates into FALSE logged_in_once and 1+ logins translates to TRUE logged_in_once. So the intermediate table will look something like that:

month	year	client_uk	logins_count
11	2019	foo123	1
12	2019	foo123	0
11	2019	bar456	1
12	2019	bar456	0

Let’s combine everything into the final query:

WITH client_uk_logins AS (
  SELECT '2019-11-03'::date AS day_of_month, 'foo123' AS client_uk, TRUE AS flag_login
  UNION
  SELECT '2019-12-04', 'foo123', FALSE
  UNION
  SELECT '2019-11-05', 'bar456', TRUE
  UNION
  SELECT '2019-12-12', 'bar456', FALSE
), client_uk_login_counts AS (
  SELECT
    DATE_PART('month', day_of_month) AS m,
    DATE_PART('year', day_of_month) AS y,
    client_uk,
    COUNT(CASE WHEN flag_login = TRUE THEN client_uk END) AS logins_count
  FROM client_uk_logins
  GROUP BY 1, 2, 3
)

SELECT
  m,
  y,
  client_uk,
  logins_count > 0 AS logged_in_once
FROM client_uk_login_counts

I hope I understood the problem correctly, ping me if I can help more

How to identify client login or not for one month

REPLIES

WRITE A REPLY