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

Database
akt
Not applicable

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.