SQL queries, select records with specific datatime occurrences (3 per week, 3 days in a row )

I'm studying SQL and I stumbled on some exercises where I got no idea how to approach the problem.

I have 2 Tables:
sys_users containing id username
sys_user_sessions containing id user_id login_tstamp logout_tstamp

I have been asked to query:

  • All users that login at least 3 times a week in the past month
  • The most common session durations in 30 min blocks for the past month
  • Users who logged at least 3 consecutive days in the past 2 months

Link to tables: http s://drive.google.com/file/d/1XNKSMVVfUzPyDXmEnBl-BWUi9O17doTu/view?usp=share_link (remove space between http and s if you wish to download the tables)

What I managed to do currently:

$todayDate = 1639033043;
$oneMonthAgoDate = $todayDate - 2419200;
SELECT * FROM `sys_users`
INNER JOIN sys_user_sessions ON sys_users.id=sys_user_sessions.user_id 
WHERE sys_user_sessions.login_tstamp 
BETWEEN '" .  strval($oneMonthAgoDate) .  "' 
AND '" . strval($todayDate) .  "'
HAVING COUNT(DISTINCT sys_user_sessions.login_tstamp ) >= 3

EXPECTED RESULTS: test_comunic user

Please be aware that this is a SQL Server forum. Someone here may be able to help with mySQL, but that is not the intended target audience of this site.

Thank you for pointing it out, if it's the case I will remove the post.

hi hope this helps

just to give you an idea
this will work in T-SQL Microsoft SQL Server
* All users that login at least 3 times a week in the past month

Real Question is
How are YOU going to be able to this from scratch ?
Point is "Learning how to Learn"

I don't believe MySQL has a "DATEPART" function.

There is no DATEPART but I can try using YEARWEEK() , this should return the week number.
"Learning how to Learn" Yeah you right, I struggle finding proper examples to try my own queries, any suggestion would be useful :slight_smile:

Let me try read your example
we SELECT id, number of records, week of the year
FROM users table that match the user_id of the JOIN (ON a.id = b.user_id)
WHERE (this is part of the JOIN) the login date is less equal than a month ago (datediff())

this way we got users, records and week, then we GROUP BY users and WEEK (this is something astounishing to me if I got it!!) So we should have all records of users for each week (1,2,3,4)

AND finally we can filter only the users that have Count >= 3 this should do the trick.

Much appreciated, let me know if I got the logic, going to try it :slight_smile:

This was so counter intuitive, but I have almost the desired results:

SELECT sys_user_sessions.user_id, sys_users.username, CONCAT(LEFT (sys_users.first_name, 1), '. ', sys_users.surname) AS 'Name', Count(sys_user_sessions.login_tstamp) AS 'loginnum', WEEK(DATE_FORMAT(FROM_UNIXTIME(sys_user_sessions.login_tstamp), '%Y/%m/%d')) AS 'week'
FROM sys_users
INNER JOIN sys_user_sessions ON sys_users.id = sys_user_sessions.user_id
WHERE (sys_user_sessions.login_tstamp
BETWEEN '" .  strval($oneMonthAgoDate) .  "'
AND '" . strval($todayDate) .  "')
GROUP BY sys_user_sessions.user_id, WEEK(DATE_FORMAT(FROM_UNIXTIME(sys_user_sessions.login_tstamp), '%Y/%m/%d'))
HAVING COUNT( sys_user_sessions.login_tstamp ) >= 3

WEEK function was very picky using timestamp but now I have all the users that logged at least 3 times grouped in year weeks:

|4706791|pippobaud|p. baudo|23|46|
|4706791|pippobaud|p. baudo|11|47|
|4706791|pippobaud|p. baudo|12|48|
|4706793|test_comunic|A. BTest|3|46|
|4706793|test_comunic|A. BTest|3|47|
|4706793|test_comunic|A. BTest|3|48|
|4706793|test_comunic|A. BTest|4|49|

What can I do to further filter the list so only test_comunic is matched? I want to show only users that have 3 records in weeks 46,47,48,49
I will try using another Count after the HAVING, not sure if will work:

HAVING COUNT( sys_user_sessions.login_tstamp ) >= 3 AND COUNT( DISTINCT WEEK(DATE_FORMAT(FROM_UNIXTIME(sys_user_sessions.login_tstamp), '%Y/%m/%d'))) == 4