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.