|
|
|
|
|
|
Query
-- Client IP hitting the server the most
-- number of times in a day
SELECT Date_Val,
IPAddress_Val,
City,
COUNT(*) AS Hits
FROM ClickStream_Fact A,
IPAddress_Dimension B,
Date_Dimension C
WHERE A.ClientIP_Key = B.IPAddress_Key
AND A.Date_Key = C.Date_Key
GROUP BY Date_Val,IPAddress_Val,City
ORDER BY Hits DESC;
Example
Date_Val | IPaddress_Val | City | Hits
------------+---------------+------------+------
2000-08-06 | 172.16.2.15 | Noida | 11
2000-10-19 | 172.16.1.3 | Tokyo | 10
2000-06-05 | 172.16.2.4 | Paris | 10
2000-07-05 | 172.16.1.6 | London | 10
2000-07-29 | 172.16.1.6 | London | 10
2000-01-19 | 172.16.2.15 | Noida | 10
2000-02-10 | 172.16.0.4 | Detroit | 10