trjhtr

I am trying to get number of times (or sessions) a user re-visited the website.

What I'm trying to find out-

Get the total number of users, number of sessions in the last 30days. I need to a pie chart ultimately where it shows people who revisited-

1. more than 100 times,


2. 50-100 times,


3. less than 50 times

The problem with the current implementation I see is -

The whole set of data(like 100million rows) are being computed every time(for each query with different condition)

SELECT COUNT(A.sess) as acount, SUM(A.sess) as asum, AVG(A.sess) as aavg
FROM
(SELECT ipaddress,
 COUNT(*) AS sess
 FROM analytics
 WHERE trunc(datecreated) > getdate() - interval '30 days'
 GROUP BY trunc(datecreated), ipaddress
 HAVING COUNT(*) >= 100) AS A

UNION ALL

SELECT COUNT(B.sess) as bcount, SUM(B.sess) as bsum, AVG(B.sess) as bavg
FROM
(SELECT ipaddress,
 COUNT(*) AS sess
 FROM analytics
 WHERE trunc(datecreated) > getdate() - interval '30 days'
 GROUP BY trunc(datecreated), ipaddress
 HAVING COUNT(*) < 100 AND COUNT(*) >=50 ) AS B

UNION ALL

SELECT COUNT(C.sess) as ccount, SUM(C.sess) as csum, AVG(C.sess) as cavg
FROM
(SELECT ipaddress,
 COUNT(*) AS sess
 FROM analytics
 WHERE trunc(datecreated) > getdate() - interval '30 days'
 GROUP BY trunc(datecreated), ipaddress
 HAVING COUNT(*) < 50 ) AS C

How do I optimize the query?

MySQL version 8 supports common table expression. If you want to differentiate between the three sub queries, use a column containing literal:

WITH SESSIONS_THIS_MONTH AS (
SELECT ipaddress,
 COUNT(*) AS sess
 FROM analytics
 WHERE trunc(datecreated) > getdate() - interval '30 days'
 GROUP BY trunc(datecreated), ipaddress
)
SELECT 'MORE THAN 100' AS CATEGORY, 
 COUNT(SESSIONS_THIS_MONTH.sess) as acount, 
 SUM(SESSIONS_THIS_MONTH.sess) as asum, 
 AVG(SESSIONS_THIS_MONTH.sess) as aavg
FROM SESSIONS_THIS_MONTH
WHERE sess >= 100

UNION ALL

SELECT 'BETWEEN 50 AND 100' AS CATEGORY, 
 COUNT(SESSIONS_THIS_MONTH.sess) as bcount, 
 SUM(SESSIONS_THIS_MONTH.sess) as bsum, 
 AVG(SESSIONS_THIS_MONTH.sess) as bavg
FROM SESSIONS_THIS_MONTH
WHERE sess < 100 AND sess >=50

UNION ALL

SELECT 'LESS THAN 50' AS CATEGORY, 
 COUNT(SESSIONS_THIS_MONTH.sess) as ccount, 
 SUM(SESSIONS_THIS_MONTH.sess) as csum, 
 AVG(SESSIONS_THIS_MONTH.sess) as cavg
FROM SESSIONS_THIS_MONTH
WHERE sess < 50