get the most two recent dates for each customer

Question

basically, I need to retrieve the last two dates for customers who purchased in at least two different dates, implying there are some customer who had purchased only in one date, the data has the following form

client_id  date
1          2016-07-02
1          2016-07-02
1          2016-06-01
2          2015-06-01

and I would like to get it in the following form

client_id      previous_date     last_date
1              2016-06-01        2016-07-02

remarques:

a client can have multiple entries for the same date

a client can have entries only for one date, such customer should be discarded

Answer 1

Rank your dates with DENSE_RANK. Then group by client_id and show the last dates (ranked #1 and #2).

select
  client_id,
  max(case when rn = 2 then date end) as previous_date,
  max(case when rn = 1 then date end) as last_date
from
(
  select 
    client_id, 
    date, 
    dense_rank() over (partition by client_id order by date desc) as rn
  from mytable
)
group by client_id
having max(rn) > 1;

Answer 2

build up:

t=# create table s153 (c int,  d date);
CREATE TABLE
t=# insert into s153 values (1,'2016-07-02'), (1,'2016-07-02'),(1,'2016-06-01'),(2,'2016-06-01');
INSERT 0 4

query:

t=# with a as (
select distinct c,d from s153
)
, b as (
select c,nth_value(d,1) over (partition by c order by d) last_date, nth_value(d,2) over (partition by c order by d) prev_date
from a
)
select * from b where prev_date is not null
;
 c | last_date  | prev_date
---+------------+------------
 1 | 2016-06-01 | 2016-07-02
(1 row)