Let's say I have the following 2 tables

Table 1: User roles

Table 2: User Sessions

Desired Outcome:

Can anyone help me get to avg days between sessions by user type?

CodePudding user response：

some ways would think of doing:

1. select from user_sessions table ordering by user_id asc & session_created_at desc, where create column that is row number partioned over user_id. lets call this table ordered_sessions
2. join table #1 against itself such that row_num = row_num 1 AND user_id = user_id. to get a table with columns of user_session and other column that is corresponding previous day of user.
3. use some datediff function (example link against the two session_created_at columns in table #2 to get days diference
4. join table #3 against user_roles table ON user_id
5. average over column from #3 group by user_type.Type

CodePudding user response：

In MySQL 8 You can use the window function LAG, to get the difference between two login Dates

CREATE TABLE User_roles (
  `User_ID` INTEGER,
  `Type` VARCHAR(10)
);

INSERT INTO User_roles
  (`User_ID`, `Type`)
VALUES
  ('1', 'Admin'),
  ('2', 'EE'),
  ('3', 'Contractor'),
  ('5', 'Admin');

CREATE TABLE User_Sessions (
  `User_ID` INTEGER,
  `Session_ID` INTEGER,
  `Session_created_at` VARCHAR(10)
);

INSERT INTO User_Sessions
  (`User_ID`, `Session_ID`, `Session_created_at`)
VALUES
  ('1', '8', '2021-01-01'),
  ('1', '9', '2021-01-02')
  ,
  ('1', '18', '2021-01-20'),
  ('3', '10', '2021-01-03'),
  ('5', '11', '2021-01-04'),
  ('5', '12', '2021-01-05')
  ,
  ('5', '13', '2021-01-10');

SELECT
`Type` as 'User Type',
AVG(diff_days ) As 'AVG days between login'
FROM (SELECT ur.`User_ID`, ur.`Type`,
timestampdiff(DAY, lag(us.`Session_created_at`, 1) OVER (PARTITION BY us.`User_ID` ORDER BY us.`Session_ID` ASC), us.`Session_created_at`) as diff_days 
FROM 
User_Sessions us JOIN User_roles ur ON us.User_ID = ur.User_ID) t1
WHERE diff_days IS NOT NULL
GROUP BY `User_ID`,`Type`
ORDER BY `User_ID` ASC

User Type | AVG days between login
:-------- | ---------------------:
Admin     |                 9.5000
Admin     |                 3.0000

db<>fiddle here

CodePudding user response：

You can use a self-join to find the login/logout sessions for each user_id, and then join your roles table back onto the result, computing the average while grouping on the role type:

with cte(id, sid, sc1, sc2) as (
   select s.user_id, s.session_id, s.session_created_at, min(s1.session_created_at) from sessions s 
   left join sessions s1 on s.user_id = s1.user_id and s1.session_created_at > s.session_created_at
   group by s.user_id, s.session_id, s.session_created_at
)
select r.`type`, avg(c.sc2 - c.sc1) from cte c 
left join roles r on c.id = r.user_id where c.sc1 is not null group by r.`type`