Untitled

SELECT COUNT(1) FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) BS WHERE BS.HITS_TYPE = 'EVENT' AND DATE BETWEEN '20210301' AND '20210331'-- 21,45,85,893

--NTP BASE:
IF OBJECT_ID('TEMP.NTP_FVIDS') IS NOT NULL
DROP TABLE TEMP.NTP_FVIDS
CREATE TABLE TEMP.NTP_FVIDS
WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS
SELECT HTS.FULLVISITORID, MIN(HTS.DATE) AS MIN_DATE FROM 
(SELECT FULLVISITORID, DATE FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) WHERE HITS_TYPE = 'EVENT' AND HITS_ISINTERACTION = 'TRUE' AND DATE <= '20210331' 
UNION SELECT FULLVISITORID, DATE FROM MKTGMART.HITS_ARCHIVE(NOLOCK) WHERE HITS_TYPE = 'EVENT' AND HITS_ISINTERACTION = 'TRUE' AND DATE <= '20210331') HTS
GROUP BY HTS.FULLVISITORID HAVING MIN(HTS.DATE) BETWEEN '20210101' AND '20210331'
CREATE STATISTICS STAT_FULLVISITORID ON TEMP.NTP_FVIDS(FULLVISITORID)-- FVIDS(NTP Q1): 2,75,80,247

--LATEST VISITS:
IF OBJECT_ID('TEMP.HIT_LATEST_VISIT') IS NOT NULL
DROP TABLE TEMP.HIT_LATEST_VISIT
CREATE TABLE TEMP.HIT_LATEST_VISIT
WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS
SELECT HTS.FULLVISITORID, CONVERT(VARCHAR(8),DATEADD(MONTH, -3, MAX(HTS.DATE)),112) AS START_DATE, MAX(HTS.DATE) AS END_DATE 
FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) HTS 
INNER JOIN TEMP.NTP_FVIDS(NOLOCK) BS ON HTS.FULLVISITORID = BS.FULLVISITORID
WHERE HTS.HITS_TYPE = 'EVENT' AND HTS.DATE BETWEEN '20210301' AND '20210331' AND HTS.HITS_ISINTERACTION = 'TRUE' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/TVC_VP/%' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/DG[_]%' 
AND HITS_PAGE_PAGEPATH NOT LIKE '/LEADMODEL%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%DEV%' AND HITS_PAGE_PAGEPATH NOT LIKE '/PPLONLINE%' AND HITS_PAGE_PAGEPATH NOT LIKE '/CUSTOMER/FORGETPASSWORD%' 
AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '%THANK%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '[0-9]%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE 'UAT%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%ADMIN%' 
AND HTS.HITS_PAGE_HOSTNAME NOT IN ('CUSTOMER360.BAJAJFINSERV.IN','','CONT-SITES-OLD.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINESERV.IN', 'CONT-SITES-NEW.BAJAJFINSERV.IN', 'CONT-SITES1.BAJAJFINSERV.IN','LOCALHOST')
GROUP BY HTS.FULLVISITORID
CREATE INDEX FULLVISITORID ON TEMP.HIT_LATEST_VISIT(FULLVISITORID)
CREATE INDEX START_DATE ON TEMP.HIT_LATEST_VISIT(START_DATE)
CREATE INDEX END_DATE ON TEMP.HIT_LATEST_VISIT(END_DATE)
CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HIT_LATEST_VISIT(FULLVISITORID)-- NTP FVIDS ACTIVE IN MARCH-2021: 1,29,87,840
--SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HIT_LATEST_VISIT(NOLOCK)

-- CLEARNING HITS DATA FOR EXTRA #'S
IF OBJECT_ID('TEMP.HITS_CLEAN_BASE_1') IS NOT NULL
DROP TABLE TEMP.HITS_CLEAN_BASE_1
CREATE TABLE TEMP.HITS_CLEAN_BASE_1
WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS
SELECT HTS.FULLVISITORID, HTS.DATE AS HIT_DATE, HTS.VISITID, HTS.HITS_HITNUMBER, HTS.HITS_PAGE_HOSTNAME, VISITSTARTTIME,
CASE WHEN CHARINDEX('#',HTS.HITS_PAGE_PAGEPATH)>1 THEN LEFT(HTS.HITS_PAGE_PAGEPATH, CHARINDEX('#',HTS.HITS_PAGE_PAGEPATH)-1) ELSE HTS.HITS_PAGE_PAGEPATH END AS HITS_PAGE_PAGEPATH,
HTS.HITS_EVENTINFO_EVENTACTION, HTS.HITS_EVENTINFO_EVENTLABEL
FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) HTS 
INNER JOIN TEMP.HIT_LATEST_VISIT(NOLOCK) AB ON HTS.FULLVISITORID = AB.FULLVISITORID AND HTS.DATE BETWEEN AB.START_DATE AND AB.END_DATE
WHERE HTS.HITS_TYPE = 'EVENT' AND HTS.HITS_ISINTERACTION = 'TRUE' 
AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/TVC_VP/%' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/DG[_]%' AND HITS_PAGE_PAGEPATH NOT LIKE '/LEADMODEL%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%DEV%'
AND HITS_PAGE_PAGEPATH NOT LIKE '/PPLONLINE%' AND HITS_PAGE_PAGEPATH NOT LIKE '/CUSTOMER/FORGETPASSWORD%' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '%THANK%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '[0-9]%' 
AND HTS.HITS_PAGE_HOSTNAME NOT LIKE 'UAT%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%ADMIN%' AND HITS_EVENTINFO_EVENTACTION NOT IN ('USER LOCATION','USER LOCATION ERROR')
AND HTS.HITS_PAGE_HOSTNAME NOT IN ('CUSTOMER360.BAJAJFINSERV.IN','','CONT-SITES-OLD.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINESERV.IN', 'CONT-SITES-NEW.BAJAJFINSERV.IN', 'CONT-SITES1.BAJAJFINSERV.IN','LOCALHOST')
CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HITS_CLEAN_BASE_1(FULLVISITORID)--(9,77,93,547 AFFECTED); FVIDS: 91,59,136
--SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HITS_CLEAN_BASE_1(NOLOCK)

-- CLEARNING HITS DATA FOR EXTRA &'S
IF OBJECT_ID('TEMP.HITS_CLEAN_BASE_2') IS NOT NULL
DROP TABLE TEMP.HITS_CLEAN_BASE_2
CREATE TABLE TEMP.HITS_CLEAN_BASE_2
WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS
SELECT BS.FULLVISITORID, BS.HIT_DATE, BS.VISITID, BS.HITS_HITNUMBER, BS.VISITSTARTTIME, BS.HITS_PAGE_HOSTNAME, BS.HITS_PAGE_PAGEPATH AS OLD, BS.HITS_EVENTINFO_EVENTACTION, BS.HITS_EVENTINFO_EVENTLABEL,
CASE WHEN CHARINDEX('&',HITS_PAGE_PAGEPATH)>1 THEN LEFT(HITS_PAGE_PAGEPATH, CHARINDEX('&',HITS_PAGE_PAGEPATH)-1) ELSE BS.HITS_PAGE_PAGEPATH END AS HITS_PAGE_PAGEPATH
FROM TEMP.HITS_CLEAN_BASE_1(NOLOCK) BS
CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HITS_CLEAN_BASE_2(FULLVISITORID)--(9,77,93,547 ROWS AFFECTED); FVIDS: 91,59,136
--SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HITS_CLEAN_BASE_2(NOLOCK)

-- CLEARNING HITS DATA FOR EXTRA ?'S
IF OBJECT_ID('TEMP.HITS_CLEAN_BASE_FINAL') IS NOT NULL
DROP TABLE TEMP.HITS_CLEAN_BASE_FINAL
CREATE TABLE TEMP.HITS_CLEAN_BASE_FINAL
WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS
SELECT BS.FULLVISITORID, BS.HIT_DATE, BS.VISITID, CAST(BS.HITS_HITNUMBER AS INT) HITS_HITNUMBER, BS.HITS_PAGE_HOSTNAME, BS.HITS_PAGE_PAGEPATH AS OLD, DATEADD(S,CAST(BS.VISITSTARTTIME AS BIGINT),'19700101 05:30:00:000') AS VISITSTARTTIME,
CASE WHEN CHARINDEX('?',HITS_PAGE_PAGEPATH)>1 THEN LEFT(HITS_PAGE_PAGEPATH, CHARINDEX('?',HITS_PAGE_PAGEPATH)-1) ELSE BS.HITS_PAGE_PAGEPATH END AS HITS_PAGE_PAGEPATH, BS.HITS_EVENTINFO_EVENTACTION, BS.HITS_EVENTINFO_EVENTLABEL
FROM TEMP.HITS_CLEAN_BASE_2(NOLOCK) BS
GROUP BY BS.FULLVISITORID, BS.HIT_DATE, BS.VISITID, CAST(BS.HITS_HITNUMBER AS INT), BS.HITS_PAGE_HOSTNAME, BS.HITS_PAGE_PAGEPATH, DATEADD(S,CAST(BS.VISITSTARTTIME AS BIGINT),'19700101 05:30:00:000'),
CASE WHEN CHARINDEX('?',HITS_PAGE_PAGEPATH)>1 THEN LEFT(HITS_PAGE_PAGEPATH, CHARINDEX('?',HITS_PAGE_PAGEPATH)-1) ELSE BS.HITS_PAGE_PAGEPATH END, BS.HITS_EVENTINFO_EVENTACTION, BS.HITS_EVENTINFO_EVENTLABEL
CREATE INDEX FULLVISITORID ON TEMP.HITS_CLEAN_BASE_FINAL(FULLVISITORID)
CREATE INDEX VISITID ON TEMP.HITS_CLEAN_BASE_FINAL(VISITID)
CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HITS_CLEAN_BASE_FINAL(FULLVISITORID)--(9,74,86,098 ROWS AFFECTED); FVIDS : 91,59,136
--SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HITS_CLEAN_BASE_FINAL(NOLOCK)