ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;
select table1.contact_id,table1.contact_date,table1.people_login
,count(contact_id) over (partition by people_login order by table1.creation_datetime asc range numtodsinterval(365, ‘day’) preceding) as total_contacts
,sum(case when contact_type_code=’p’ then 1 else 0 end) over (partition by people_login order by table1.creation_datetime asc range numtodsinterval(365, ‘day’) preceding) as phone_contacts
From contacts table1
where table1.marketplace=232435465
This sql code calculated past year (365 days before table1.creation_datetime until table1.creation_datetime) the number of phone contacts for each contact_id.