Everyday SQL (4)

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.