/*========================================================*/
/* Web page and web ads performance analytics & metrics   */
/* All performed on demo2 page_tracker table              */
/*========================================================*/

/*===============================================================================*/
/* How many times have customers been interested in a particular page or product */ 
/* (in this case product "Track") in the past 100 days?                          */
/*===============================================================================*/
SELECT client_ip, count(*) "total requests"
FROM   page_tracker
WHERE  requested_url LIKE  '%Track%'                    --substitute the page or product name
  AND  date_time > TIMESTAMPADD(day,-100,now() )        --in the past 100 days
GROUP BY client_ip
ORDER BY count(*) DESC;


/*===============================================================================*/
/* Which is the most popular page (or product) as viewed by each customer?       */ 
/*===============================================================================*/
SELECT SUBSTRING(requested_url, LENGTH(requested_url)-17) AS page,  --extract the end of the url
	   client_ip,  COUNT(*) AS viewed 
FROM   page_tracker
WHERE requested_url  LIKE '%html'                                   --filter to eliminate certain pages
GROUP BY page, client_ip
ORDER BY viewed DESC;


/*===============================================================================*/
/* Rank ads by % performance of clicks against views                             */ 
/*===============================================================================*/
SELECT inner_data.*,  clicks/views*100 AS performance
FROM 
  ( SELECT ad_name, 	COUNT( CASE action WHEN 'view' THEN 1 END) AS views,
	COUNT( CASE action WHEN 'click' THEN 1 END) AS clicks
     FROM page_tracker
	WHERE ad_name IS NOT NULL
    GROUP BY ad_name 
  ) AS inner_data
ORDER BY performance DESC