title: Oracle sql query that returns customers who are coming for the first time in property
body: <p>I have 3 tables: <strong>customer</strong>, <strong>property</strong> and <strong>stays</strong>. Table customer contains all the data about customers (<code>customer_id, name, surname, email...</code>). Table property contains the list of all the properties (<code>property_id, property_name...</code>) and table stays contains all the earlier stays of customers (<code>customer_id, property_id, stay_id, arrival_date, departure_date...</code>).</p>
  
  <p>Some customers stayed multiple times in more than one properties and some customers are coming for the first time. 
  Can someone please explain oracle sql query which returns only the customers who are stying in any of the properties for the first time.</p>
  
  <p>Sorry guys for answering late..
  This is what I got so far.</p>
  
  <p>Tables:
  Customer             $A$</p>
  
  <p>Stays                $B$ </p>
  
  <p>Property             $C$</p>
  
  <p>Customer_Fragments   $D$ </p>
  
  <pre><code>SELECT a.RIID_, 
    sub.CUSTOMER_ID_, 
    sub.PROPERTY_ID, 
    b.ARRIVAL_DATE, 
    c.PROPERTY_SEGMENT, 
    ROW_NUMBER() OVER (
      PARTITION BY sub.CUSTOMER_ID_, 
      sub.PROPERTY_ID 
      ORDER BY 
        sub.CUSTOMER_ID_ asc
    ) RN 
  FROM 
    (
      SELECT 
        b.CUSTOMER_ID_, 
        b.PROPERTY_ID 
      FROM 
        $B$ b 
      GROUP BY 
        b.CUSTOMER_ID_, 
        b.PROPERTY_ID 
      HAVING 
        COUNT(*)= 1
    ) sub 
    INNER JOIN $A$ a ON sub.CUSTOMER_ID_ = a.CUSTOMER_ID_ 
    INNER JOIN $B$ b ON sub.CUSTOMER_ID_ = b.CUSTOMER_ID_ 
    INNER JOIN $C$ c ON sub.PROPERTY_ID = c.PROPERTY_ID 
    LEFT JOIN $D$ d ON a.RIID_ = d.RIID_ 
  WHERE 
    b.ARRIVAL_DATE = TRUNC(SYSDATE + 7) 
    AND c.PROPERTY_DESTINATION = 'Destination1' 
    AND lower(c.NAME_) NOT LIKE ('unknown%') 
    AND a.NWL_PERMISSION_STATUS = 'I'  
    AND a.EMAIL_DOMAIN_ NOT IN ('abuse.com', 'guest.booking.com')
    AND (d.BLACKLISTED != 'Y' 
      or d.BLACKLISTED is null
    )
  </code></pre>
  
  <p>I want to select all customers who will come to Destination1, 7days from today to inform them about some activities. Customers can book several
  properties in Destination1 and have the same arrival date (example: I can book a room in property1 for me and my wife and also book a room in property2 for my friends.. and we all come to destination1 on the same arrival date).
  When this is the case I want to send just one info email to a customer and not two emails. The above SQL query returns two rows when this is the case and I want it to return just one row (one row = one email). </p>