ranking code - metalvana/iCrossingCP GitHub Wiki

/*Title: Path flow Description: Creates a path through the website by each ic_id, with a rank indicating the order in which pages were viewed. Author: Kate Wright Created: 9.30.14 Edited: 9.30.14 by Kate Notes: the rank function will take time to run, might need to disable your query timeout function. With the amount of data it could be around ~ 1 hour. */

Select ch.ic_id ,ch.referrer_domain ,ch.url_domain ,ch.url ,ch.traffic_source ,rank() over (order by ch.hittime_gmt asc)as "rank" ,ch.ua_device_type ,ch.channel ,ch.search_term ,ch.page_name ,ch.ip_address

--create temp table with info for quicker query time Into #ranked From hap_prod.hap_fact_cornell_hits ch

Where ch.hittime_gmt >= '2014-07-01 00:00' Group by ch.ic_id,ch.referrer_domain ,,ch.hittime_gmt ,ch.url_domain ,ch.url ,ch.traffic_source ,ch.ua_device_type ,ch.channel ,ch.search_term ,ch.page_name ,ch.ip_address ;