|
|
|
|
|
|
Query
-- Customers creating more than 5 sessions per day
SELECT Date_Val,
Customer_Name,
SUM(Duration),
COUNT(*) AS Count_Session
FROM ClickStream_Fact A,
Date_Dimension B,
Session_Dimension C,
Customer_Dimension D
WHERE A.Date_Key = B.Date_Key
AND A.Customer_Key = D.Customer_Key
AND A.Session_Key = C.Session_Key
GROUP BY Date_Val,Customer_Name,Duration
HAVING COUNT(*) > 5
ORDER BY Duration DESC;
Example
Date_Val | Customer_Name | SUM | Count_Session
------------+---------------+------+---------------
2000-06-29 | Matthew | 1320 | 11
2000-07-08 | Hannah | 1200 | 10
2000-07-11 | Hannah | 960 | 8
2000-07-12 | Hannah | 840 | 7
2000-07-13 | Hannah | 1800 | 15
2000-07-15 | Hannah | 1920 | 16