title: Duration between two customer orders
body: <p>I'm having some difficulty with a SQL query. There is a table name Customer_Orders with the following columns</p>
  
  <pre><code>Order_ID INT -Primary Key  
  Customer_ID INT - Customer who made the transaction  
  Order_Time_Stamp DATE - Date and time of transaction
  Item STRING - Description of item purchased
  Transaction_Status STRING - Status can be "Success" and "Failure"
  </code></pre>
  
  <p>How can we get the duration in days between the first order and the second order for each customer within 30 days only for customers whose first order is a success?</p>
  
  <p>My query logic is something like:</p>
  
  <pre><code>select Customer_ID, Duration_Between_Order_In_Days
  from Customer_Orders
  where DATEDIFF(DAYS, First_Order_Date, Second_Order_Date) &lt; 30
  and First_Order_Status='Success'
  </code></pre>
  
  <p>I have tried using DATEDIFF() function and self join and sub query, but I don't know how to find the second order for each customer. Any ideas would be appreciated!</p>