|
|
|
|
|
|
Query
-- Page with the maximum number of hits
-- and total browsing time
SELECT Date_Val,
Page_Name,
SUM(Browsing_Time) AS Browsing_Time,
COUNT(*) AS Hits
FROM ClickStream_Fact A,
Page_Dimension B,
Date_Dimension C
WHERE A.Date_Key = C.Date_Key
AND A.Page_Key = B.Page_Key
GROUP BY Date_Val,Page_Name
ORDER BY Browsing_Time DESC,
Hits DESC;
Example
Date_Val | Page_Name | Browsing_Time | Hits
------------+------------------------------------------------+---------------+-----
2000-06-06 | http://www.Geocities.Yahoo.com/page72.html | 90 | 16
2000-11-19 | http://www.Jewellery.Rediff.com/page23.html | 87 | 11
2000-03-16 | http://www.MP3-Players.Rediff.com/page34.html | 81 | 14
2000-05-04 | http://www.Cricket.Rediff.com/page90.html | 80 | 13
2000-04-27 | http://www.Laptops.Rediff.com/page69.html | 79 | 11
2000-01-20 | http://www.Mobiles.Rediff.com/page97.html | 75 | 12