Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table transactions
as
select
  mod( level, 3 ) + 1 as srcuserid
, to_date( trunc( dbms_random.value( 2451925, 2458258 ) ), 'J' ) pickupdate
, round( dbms_random.value() * 10000, 2 ) pricetotal
from dual
connect by level <= 12 ;
12 rows affected

select * from transactions order by srcuserid, pickupdate ;


SRCUSERID
PICKUPDATE
PRICETOTAL


1
23-OCT-02
630.72

1
16-AUG-08
3596.88

1
31-AUG-09
5128.27

1
17-JAN-10
3450.3

2
22-APR-03
2656.01

2
11-DEC-05
4911.89

2
08-OCT-13
9483.72

2
21-JUL-16
9997.48

3
17-JUN-08
159.72

3
19-JUN-12
649

3
26-NOV-12
2348.6

3
13-MAR-13
7263.25


select
  srcuserid
, count_ - 1          as frequency
, max_date - min_date as recency
, trunc( sysdate - min_date )  as T
, case
    when count_ - 1 = 0 then 0
    else round( ( sum_pricetotal - firstpricetotal ) / ( count_ - 1 ), 2 ) 
  end as monetary_value 
from (
  select distinct
    srcuserid
  , count( pickupdate ) over ( partition by srcuserid ) as count_
  , max( pickupdate ) over ( partition by srcuserid )   as max_date
  , min( pickupdate ) over ( partition by srcuserid )   as min_date
  , sum( pricetotal ) over ( partition by srcuserid )   as sum_pricetotal
  , first_value( pricetotal ) over ( 
      partition by srcuserid order by pickupdate )      as firstpricetotal
  from transactions
) 
;


SRCUSERID
FREQUENCY
RECENCY
T
MONETARY_VALUE


2
3
4839
5986
8131.03

3
3
1730
4103
3420.28

1
3
2643
6167
4058.48

SRCUSERID	PICKUPDATE	PRICETOTAL
1	23-OCT-02	630.72
1	16-AUG-08	3596.88
1	31-AUG-09	5128.27
1	17-JAN-10	3450.3
2	22-APR-03	2656.01
2	11-DEC-05	4911.89
2	08-OCT-13	9483.72
2	21-JUL-16	9997.48
3	17-JUN-08	159.72
3	19-JUN-12	649
3	26-NOV-12	2348.6
3	13-MAR-13	7263.25