SQL For Unique, Repeat, and New Visitors

First, deploy the custom SQL executor package and SQL property file by using the custom SQL executor instructions. Ignore this step if you already deployed the custom SQL executor package.

SQL For Unique, Repeat, and New Visitors is given below:

Unique Visitors:

    MySql DB: 

SELECT COUNT(DISTINCT ip) FROM eum_entry USE INDEX (eum_entry_ix) 
WHERE recorded BETWEEN NOW() - INTERVAL 15 MINUTE AND NOW()

    Oracle DB:

SELECT /*+ INDEX(eum_entry eum_entry_ix) */ count(distinct ip) 
FROM eum_entry WHERE recorded BETWEEN systimestamp  - INTERVAL '15' MINUTE AND systimestamp

    Derby DB:

SELECT COUNT(DISTINCT ip) as count from eum_entry  -- DERBY-PROPERTIES index = eum_entry_ix \r   
where recorded BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE,-15,CURRENT_TIMESTAMP)} and CURRENT_TIMESTAMP

Repeat Visitors:

    MySql DB:

SELECT COUNT(*) FROM (SELECT ip, COUNT(*) AS COUNT FROM eum_entry 
USE INDEX (eum_entry_ix) WHERE recorded BETWEEN NOW() - INTERVAL 15 MINUTE 
AND NOW() GROUP BY ip) uem WHERE COUNT>1

    Oracle DB:

SELECT COUNT(*) FROM (SELECT /*+ INDEX(eum_entry eum_entry_ix) */ ip, 
COUNT(*) AS COUNT FROM eum_entry WHERE recorded BETWEEN 
systimestamp  - INTERVAL '15' MINUTE AND systimestamp  
GROUP BY ip) uem WHERE COUNT>1

   Derby DB:

SELECT COUNT(*) FROM (SELECT ip, COUNT(*) 
AS COUNT FROM eum_entry -- DERBY-PROPERTIES 
index = eum_entry_ix \r WHERE recorded 
BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE,-15,CURRENT_TIMESTAMP)} 
and CURRENT_TIMESTAMP GROUP BY ip) uem WHERE COUNT>1

New Visitor:

    MySql DB:

SELECT COUNT(DISTINCT ip) FROM eum_entry USE INDEX (eum_entry_ix) 
WHERE ip NOT IN (SELECT DISTINCT ip FROM eum_entry 
USE INDEX (eum_entry_ix) WHERE recorded < NOW() - INTERVAL 15 MINUTE )

    Oracle DB:

SELECT /*+ INDEX(eum_entry eum_entry_ix) */ COUNT(DISTINCT ip) 
FROM eum_entry WHERE ip NOT IN (SELECT /*+ INDEX(eum_entry eum_entry_ix) */ 
DISTINCT ip FROM eum_entry WHERE recorded < systimestamp  - INTERVAL '15' MINUTE)

  Derby DB:

SELECT COUNT(DISTINCT ip) FROM eum_entry -- DERBY-PROPERTIES index = eum_entry_ix \r  
WHERE ip NOT IN ( SELECT DISTINCT ip 
FROM eum_entry -- DERBY-PROPERTIES index = eum_entry_ix \r 
WHERE recorded < {fn TIMESTAMPADD(SQL_TSI_MINUTE,-15,CURRENT_TIMESTAMP)})

Bounce Rate (the percentage of visits that are single page):

    MySQL:

SELECT COUNT(c)/(SELECT COUNT(*) AS total FROM eum_entry 
USE INDEX (eum_entry_ix) WHERE recorded 
BETWEEN NOW() - INTERVAL 15 MINUTE AND NOW()) * 100  
AS percentage FROM (SELECT COUNT(*) AS c FROM eum_entry e 
USE INDEX (eum_entry_ix) , eum_url u WHERE e.page_id=u.id 
AND e.recorded BETWEEN NOW() - INTERVAL 15 MINUTE 
AND NOW() GROUP BY e.ip, u.url ) AS temp WHERE c = 1

    Oracle:

SELECT COUNT(c)/total_cnt * 100 as perc FROM (SELECT  /*+ 
INDEX(eum_entry eum_entry_ix) */ COUNT(*) 
AS c,  sum(count(*)) over() total_cnt 
FROM eum_entry e, eum_url u WHERE e.page_id=u.id  and e.recorded 
BETWEEN systimestamp - INTERVAL '15' MINUTE 
AND systimestamp GROUP BY ip, url ) temp 
WHERE c = 1 group by total_cnt

    Derby:

SELECT (select count(*) from (SELECT   COUNT(*) AS c 
FROM eum_entry e -- DERBY-PROPERTIES index = eum_entry_ix \r   
, eum_url u WHERE e.page_id=u.id  and e.recorded 
BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE,-15, CURRENT_TIMESTAMP)} and 
CURRENT_TIMESTAMP GROUP BY ip, url ) temp 
WHERE c = 1) * 100/ (SELECT COUNT(*) AS total FROM eum_entry 
-- DERBY-PROPERTIES index = eum_entry_ix \r   WHERE recorded 
BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE,-15,CURRENT_TIMESTAMP)} and 
CURRENT_TIMESTAMP) as perc  from eum_entry -- DERBY-PROPERTIES 
index = eum_entry_ix \r   fetch first 1 rows only

Visit duration (average amount of time in seconds a visitor spends on the site): 

  
    MySQL:

SELECT AVG(diff) FROM (SELECT TIME_TO_SEC(TIMEDIFF(MAX(recorded), 
MIN(recorded))) diff FROM eum_entry USE INDEX (eum_entry_ix) 
WHERE recorded BETWEEN NOW() - INTERVAL 15 MINUTE AND NOW() 
GROUP BY ip) temp

    Oracle:

SELECT AVG(diff) FROM (SELECT /*+ INDEX(eum_entry eum_entry_ix) */  
EXTRACT (DAY    FROM (MAX(recorded)-MIN(recorded)))*24*60*60+       
EXTRACT (HOUR   FROM (MAX(recorded)-MIN(recorded)))*60*60+       
EXTRACT (MINUTE FROM (MAX(recorded)-MIN(recorded)))*60+         
EXTRACT (SECOND FROM (MAX(recorded)-MIN(recorded))) diff  from eum_entry 
WHERE recorded BETWEEN systimestamp - INTERVAL '15' MINUTE 
AND systimestamp GROUP BY ip) temp

    Derby:

select avg({fn timestampdiff(SQL_TSI_SECOND, starttime, endtime)} ) 
as average from (SELECT MIN(recorded) starttime, MAX(recorded) endtime  
FROM eum_entry -- DERBY-PROPERTIES index = eum_entry_ix \r 
WHERE recorded BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE, -15, CURRENT_TIMESTAMP)} 
and CURRENT_TIMESTAMP GROUP BY ip) temp

Exit Rate (the percentage of visits seeing a page where that page is the final page viewed in the visit): 

    MySQL:

SELECT (SELECT COUNT(*) FROM (SELECT ip, COUNT(*) 
AS COUNT FROM eum_entry USE INDEX (eum_entry_ix) 
WHERE recorded BETWEEN NOW() - INTERVAL 15 MINUTE 
AND NOW() GROUP BY ip) uem WHERE COUNT=1)/ (SELECT COUNT(DISTINCT ip) 
FROM eum_entry USE INDEX (eum_entry_ix) WHERE recorded 
BETWEEN NOW() - INTERVAL 15 MINUTE AND NOW()) * 100 AS exit_Rate

  Oracle:

SELECT  r.single_page_visitors/o.total_user * 100 perc 
from (SELECT COUNT(*) as single_page_visitors 
FROM (SELECT /*+ INDEX(eum_entry eum_entry_ix) */ ip, 
COUNT(*) AS COUNT FROM eum_entry  WHERE recorded 
BETWEEN systimestamp  - INTERVAL '15' MINUTE AND systimestamp  
GROUP BY ip) uem WHERE COUNT=1) r cross join  
(SELECT /*+ INDEX(eum_entry eum_entry_ix) */ 
COUNT(DISTINCT ip) total_user FROM eum_entry WHERE recorded 
BETWEEN systimestamp  - INTERVAL '15' MINUTE AND systimestamp ) o

  Derby:

SELECT (SELECT COUNT(*) FROM (SELECT  ip, COUNT(*) 
AS COUNT FROM eum_entry  -- DERBY-PROPERTIES 
index = eum_entry_ix \r  WHERE recorded 
BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE, -15, 
CURRENT_TIMESTAMP)} and CURRENT_TIMESTAMP  
GROUP BY ip) uem WHERE COUNT=1) *100 / 
(SELECT COUNT(DISTINCT ip) FROM eum_entry 
-- DERBY-PROPERTIES index = eum_entry_ix \r  
WHERE recorded BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE, 
-15, CURRENT_TIMESTAMP)} and CURRENT_TIMESTAMP )  
from eum_entry -- DERBY-PROPERTIES index = eum_entry_ix \r  
fetch first 1 rows only

 Frequency ( total number of visits divided by total number of unique visitors):

    MySQL:

SELECT (SELECT COUNT(*) FROM eum_entry 
USE INDEX (eum_entry_ix) WHERE recorded 
BETWEEN NOW() - INTERVAL 15 MINUTE AND 
NOW())/(SELECT COUNT(DISTINCT ip) FROM eum_entry 
USE INDEX (eum_entry_ix) WHERE recorded 
BETWEEN NOW() - INTERVAL 15 MINUTE AND NOW()) AS freq

   Oracle:

SELECT r.total/o.unique_user AS freq from 
(SELECT /*+ INDEX(eum_entry eum_entry_ix) */ 
COUNT(*) total FROM eum_entry  WHERE recorded 
BETWEEN systimestamp - INTERVAL '15' MINUTE 
AND systimestamp) r cross join (SELECT /*+ 
INDEX(eum_entry eum_entry_ix) */ COUNT(DISTINCT ip) unique_user 
FROM eum_entry WHERE recorded BETWEEN systimestamp - 
INTERVAL '15' MINUTE AND systimestamp) o

   Derby:

select (SELECT count(*) FROM eum_entry -- DERBY-PROPERTIES 
index = eum_entry_ix \r  WHERE recorded 
BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE, -15, 
CURRENT_TIMESTAMP)} and CURRENT_TIMESTAMP)/ 
(SELECT COUNT(DISTINCT ip) FROM eum_entry 
-- DERBY-PROPERTIES index = eum_entry_ix \r  
WHERE recorded BETWEEN {fn TIMESTAMPADD(SQL_TSI_MINUTE, 
-15, CURRENT_TIMESTAMP)} and CURRENT_TIMESTAMP)  from eum_entry 
-- DERBY-PROPERTIES index = eum_entry_ix \r    
fetch first 1 rows only

Note: You have to choose the SQL  according to your Applicare server's database, because the above queries are based on that database.

The above queries will return the data for last 15 minutes. You can can change it if you wish. Use a unique name for each SQL in the SQLProperty.props file.