SQL Query: traffic referred from the same resource

CREATE temp TABLE VISITKEY_LOOKUP AS
SELECT VISIT_KEY
FROM TABLE_PAGE_VIEWS
WHERE SITE_KEY = 1
and day_key >= 20080501
AND REFERRER like '%google%'
AND VISITORIZED='true'
group by 1;



--PV
SELECT DAY_KEY || SUBSTRING(Event_DTM from 11 for 3) as DAY_HOUR, count(*) PAGEVIEW
FROM TABLE_PAGE_VIEWS PV
INNER JOIN VISITKEY_LOOKUP
ON PV.VISIT_KEY=VISITKEY_LOOKUP.VISIT_KEY
WHERE SITE_KEY = 1
and day_key >= 20080501
group by 1
order by 1;