Help on 7 days average - advance sql

Database

Help on 7 days average - advance sql

hi,

I have simple table:

date user_id

20101011 adam

20101011 bob

20101013 adam

20101013 courtney

20101014 bob

20101018 adam

I tried to calculate count of unique user id for pass 7 days for each day.

i did:

select date, count(distinct user_id) over (partition by date order by date range between date - 7 and current row)

from table

it gave me error: expecting reset for order by clause.

Please help.

2 REPLIES
mjj
Teradata Employee

Re: Help on 7 days average - advance sql

Hope this help:

select "date", count(user_id) over (partition by "date" order by "date" rows between 7 preceding and current row)

from x1

Rgds,

Enthusiast

Re: Help on 7 days average - advance sql

Hi

with recursive recur1(name1,d1,d2) as ( select name,date1- 7,date1 from dateex)

select name,d2,count(*) from recur1 inner join dateex on name1 = name where date1 between d1 and d2 group by name,d2 order by name;

I hope this fulfil's ur requirement

Regards

Hari