Help needed in selecting customers based on the property visiting dates and aggregating them into property level

Database
akt
N/A

Help needed in selecting customers based on the property visiting dates and aggregating them into property level

Hi,

I have customers transaction table visiting certain properties over several years. The dataset contains customerid, property_name, visited_date and has over 50million records.

I would like to aggregate the customer information into properties related as shown below

               property1_column   property2_column   property3_column  

property1        value1                    value2...

property2

proerty 3

The criteria to fill each field : For example if we take value2, "value2" shoud be the count of customers who visited "property2_column" prior to the latest visting date on "property1".

I'm using teradta 14.1. I would greatly appreciate your help in developing the query.