Getting how many days users have logged in during a month (same day counts as 1 log in)



  • Let's see if you can help me with a query. I want to know how many times users have logged in during a month.

    If I have this data:

    id_log id_user timestamp
    1 1 2022-02-22 00:00:00
    2 1 2022-02-22 15:00:00
    3 1 2022-02-23 00:00:00
    4 2 2022-02-22 00:00:00
    5 3 2022-02-22 00:00:00
    6 3 2022-02-24 00:00:00
    7 3 2022-02-25 00:00:00
    8 1 2022-03-01 00:00:00
    9 2 2022-03-01 00:00:00

    Result:

    year month times number_of_users_logged_in
    2022 2 1 2 (id_user 2 and 3)
    2022 2 2 1 (id_user 1)
    2022 2 3 1 (id_user 3)
    2022 3 1 2 (id_user 1 and 2)

    Notice that user 1 has logged in twice on the 22nd but only counts as one.

    Thanks in advance,

    Regards



  • I think the expected result are not correct because user 1 in 2 month is logged twice not once. 2022-02-22 00:00:00 2022-02-22 15:00:00 counts as one and the second log-in 2022-02-23 00:00:00

    Maybe this would help:

    select t1.Year,month,t1.user as times,group_concat(t1.id_user) as users_logged_in
    from ( select month(`timestamp`) as month ,
                  count(distinct id_user, 
                  date(`timestamp`)) as user ,
                  id_user,
                  year(`timestamp`) as Year
           from test_tbl
           group by   month(`timestamp`),id_user,year(`timestamp`)
           ) as t1
    group by  t1.Year,month  ,times    
    order by month asc,times asc;
    

    Results

    Year month times users_logged_in
    2022 2 1 2
    2022 2 2 1
    2022 2 3 3
    2022 3 1 1,2

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6bf296a6002517c9482746aee754ac57




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2