SQL query to get the average time spent by user on page

Question

Here is a sample table that I am using,

User_id           timestamp            action
1            2020-10-01 09:00:00    Opened page
1            2020-10-01 09:10:00    Closed page
2            2020-10-02 04:00:00    Signed up
3            2020-10-02 06:00:00    Opened page
3            2020-10-03 11:00:00    Made a booking
3            2020-10-03 09:30:00    Closed page

need to write a SQL query to find the average time spent by a user on the page.

The expected answer is just a number which represents the average time spent by an average user on the page.

Answer 1

You can’t use SQL to calculate how much time a user spends on different pages of your UI application. You will need to implement this logic on your UI whenever there is an event such as when the user navigates to another page or a button click etc. You capture the timestamps you need on the UI and then make a database call through an SP call or Query through your server side code (such as .Net, Java or Node.js).

Once you have captured the data from the UI you will be able to implement any kind of logic on that data through an SP or a function or something like that in using SQL.

Answer 2

If you use TIMESTAMPDIFF(), and set its argument to SECOND, you can get back the difference of two datetime fields in a record in a manner that can be summed and divided. Documentation:

Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions.

Then use SUM() to sum up these values, and divide by the results of COUNT(). Documentation:

SUM(): Returns the sum of expr. If the return set has no rows, SUM() returns NULL. COUNT(): Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement.

Your code will then basically look like this. You may need to make some adjustments based on your database setup.

SELECT
    SUM(
        TIMESTAMPDIFF(SECOND, OrigDateTime, LastDateTime)
    ) / (select COUNT(id) FROM yourTable)
    AS average
FROM yourTable;

This, of course, follows our standard formula for calculating an average:

sum(differences) / count(differences)