SQL & PL/SQL

Hi everyone,

An analyst on my team heard of a new metric called a "Stickiness" metric. It basically measures how often users are coming to your website overtime.

The definition is as follows:
# Unique Users Today/#Unique users Over Last 7 days
and also
# Unique Users Today/#Unique users Over Last 30 days

We have visit information stored in a table W_WEB_VISIT_F. For the sake of simplicity say it has columns VISIT_ID, VISIT_DATE and USER_ID (there are several more dimensional columns it has but I want to keep this exercise simple).

I want to create an aggregate table called W_WEB_VISIT_A that pre-aggregates the three values I need per day: # Unique Users Today, #Unique users Over Last 7 days and #Unique users Over Last 30 days. The only way I can think of building the aggregate table is as follows

WITH AGG AS (
SELECT
VISIT_DATE,
USER_ID
FROM W_WEB_VISIT_F
GROUP BY
VISIT_DATE,
USER_ID
)
select
VISIT_DATE
COUNT(DISTINCT USER_ID) UNIQUE_TODAY,
(select count(distinct hist.USER_ID) from agg hist where hist.VISIT_DATE between src.VISIT_DATE - 6 and src.VISIT_DATE) SEVEN_DAYS,
(select count(distinct hist.USER_ID) from agg hist where hist.VISIT_DATE between src.VISIT_DATE - 29 and src.VISIT_DATE) THIRTY_DAYS
from agg
group by visit_date
;

The problem I am having is that W_WEB_VISIT_F has several million records in it and I can't get it the above query to complete. It ran over night and didn't complete.

Is there a fancy 11g function I can use to do this for me? Is there a more efficient method?

Thanks everyone for the help!

-Joe

Edited by: user9208525 on Jan 13, 2011 6:24 AM

You guys are right. I missed the group by I had in the WITH Clause.