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

Transact-SQL
1

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

2

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.

3

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

4

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"

image
image768×472 11.1 KB

5

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

6

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:

7

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:

EG:
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