Calculate total time between earliest and latest visit for all visits

I am storing user's visits in the following table (with sample data):

And I need to calculate the total activity time for each user, which means to sum the time between page_visit_created_at and page_visit_last_activity for each user, per visit_id.

In the example above, the user with user_id 1 visited page1 and page2 at the same visit session, that's why they have the same visit_id, so I need to minimum page_visit_created_at for visit_id 11, which is 2023-06-21 16:56:13.950 from row 1, and the latest page_visit_last_activity which is 2023-06-21 17:04:51.122.

He also visited page1 with a different visit_id (visit 16) and once only so the minimum is 2023-06-22 13:10:11.223 and max is 2023-06-22 13:15:10.111

so the total would be the sum of the two.

The following query doesn't work:

SELECT 
    user_id, visit_id, 
    SUM(DATEDIFF(SECOND, MIN(page_visit_created_at), MAX(page_visit_last_activity))) 
FROM 
    my_table
GROUP BY 
    user_id, visit_id 

I get this error:

> Cannot perform an aggregate function on an expression containing an aggregate or a subquery

The error message indicates that using aggregate functions within aggregate functions is not permitted. one option is to calculate time spent per visit using min() and max() separately in a subquery then apply the aggregate function sum() on this dataset :

SELECT user_id, SUM(time_per_visit) as total
FROM (
  SELECT user_id, visit_id, 
     DATEDIFF(SECOND, MIN(page_visit_created_at), MAX(page_visit_last_activity)) as time_per_visit 
  FROM my_table
  GROUP BY user_id, visit_id
) AS s
GROUP BY user_id

You need to aggregate in two phases. For example:

select user_id, sum(session) as total
from (
  select
    user_id, visit_id, 
    datediff(second, min(page_visit_created_at), 
      max(page_visit_last_activity)) as session
  from t
  group by user_id, visit_id
) x
group by user_id