FORUM Everything about SQL How to identify client login or not for one month

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

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 😊

WRITE A REPLY

Sign up to reply