title: I am trying to return a query that will give me the penultimate delivery_dates of for all users
body: <p>I am working with a databricks database and I have to write a query that will return the delivery_date of the penultimate order of all users (So the penultimate delivery_date). I have all of the historical delivery_date for all of the users but I am only interested in the penultimate delivery_date of all users. </p>
  
  <p>I will try to walk you guys through what I am trying to accomplish using a simplified example. </p>
  
  <p>When I run the code below for one user.</p>
  
  <pre><code>SELECT delivery_date_local
  FROM order.orders 
  WHERE lower(customer_login_visits) = '&lt;my-email-here&gt;@gmail.com'
  </code></pre>
  
  <p>I get the following results:</p>
  
  <pre><code>Delivery_date_local
  2019-03-22
  2019-03-22
  2019-03-22
  2019-03-22
  2019-03-21
  2019-03-20
  </code></pre>
  
  <p>What I want is the penultimate delivery_date_local for this user (and all other users) which in this case is 2019-03-22 but would be different if the user doesn't have more than one order on the same day.</p>
  
  <p>This is the query I am running to get the desired results which is based on <a href="https://stackoverflow.com/questions/51171800/penultimate-date-for-each-record">a query I found on Stack Overflow</a>.</p>
  
  <pre><code>SELECT 
      DISTINCT(customer_login),
      MAX (delivery_date_local) as Last_order_date,
      MAX(case when seqnum = 2 then delivery_date_local end) as Penultimate_order_date
  FROM (
      SELECT
          DENSE_RANK() OVER(PARTITION BY customer_login_visits DESC) as seqnum
      FROM order.orders
  )order.orders
  WHERE seqnum IN (1,2)
  GROUP BY customer_login_visits
  </code></pre>
  
  <p>But I am receiving the following error:</p>
  
  <blockquote>
    <p>Error in SQL statement: ParseException: <br>
    mismatched input 'from' expecting (line 5, pos 0)</p>
  </blockquote>
  
  <pre><code>Select distinct  (customer_login),
  
             MAX(delivery_date_local) as Last_order_date,
  
             max(case when seqnum = 2 then
  
             delivery_date_local end) as Penultimate_order_date
  
  from           (select 
  ^^^
  </code></pre>
  
  <p>I have read various post online and have tried different queries but nothing seems to work.</p>