Moving average on other window function redshift

Question

I got stuck in a problem and need help on this

I have a table like this:

created_time_id | txn_src 
1-1-2017     | A
1-1-2017     | A
1-1-2017     | B
1-1-2017     | A
1-1-2017     | C
2-1-2017     | A
2-1-2017     | C
2-1-2017     | B
2-1-2017     | A
3-1-2017     | A
3-1-2017     | A
3-1-2017     | C

In redshift, I have to create a moving average column for the above table along with the source count partition by date

currently I have written the below query

select
    txn_src,
    created_time_id::char(8)::date as "time",
    count_payment
from
    (
    select
        txn_src,
        created_time_id,
        count(1) as count_payment,
        row_number() over (partition by created_time_id
    order by
        count(1) desc) as seqnum
    from
        my_table
    where
        created_time_id >= '1-1-2017' and txn_source is not null 
    group by
        1,
        2
     ) x
where
    seqnum <= 10
order by
    "time" ,
    count_payment desc

This gives me the correct output like

1-1-2017 | A | 3
1-1-2017 | B | 1

and so on

I need moving average like this

time     |src|cnt|mvng_avg
1-1-2017 | A | 3 |3
1-1-2017 | B | 1 |1
1-1-2017 | C | 1 |1
2-1-2017 | A | 2 |2.5

and so on .. Can anybody suggest some good solution for this.

Deepak Tripathi · Answer

After some struggle, I was able to resolve this using below query

with txn_source_by_date as (
select
        txn_source ,
        created_time_id,
        count(1) as count_payment, 
        row_number() over (partition by created_time_id
order by
        count(1) desc) as seqnum
from
        my_table
where
        created_time_id >= 20220801
    and txn_source is not null
group by
        1,
        2
)
select
    txn_source,
    created_time_id::char(8)::date as "time",
    count_payment,
    avg(count_payment) over (partition by txn_source  
order by
    created_time_id rows between 29 preceding and current row ) mvng_avg
from
    txn_source_by_date
group by
    txn_source,
    created_time_id,
    count_payment
order by
    "time",
    txn_source

Moving average on other window function redshift

1 Answers