Get the newest and the second newest date per value in another column-CodePudding

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    campaign VARCHAR(255),
    sales_volume VARCHAR(255)
);

INSERT INTO sales
(event_date, campaign, sales_volume)
VALUES 
('2020-01-01', 'C001', '125'),
('2020-01-02', 'C001', '300'),
('2020-01-07', 'C001', '700'),
('2020-01-12', 'C001', '900'),

('2020-01-08', 'C002', '600'),
('2020-01-10', 'C002', '465'),
('2020-02-15', 'C002', '570'),
('2020-03-22', 'C002', '780'),

('2020-02-01', 'C003', '150'),
('2020-03-17', 'C003', '325'),
('2020-03-18', 'C003', '482'),
('2020-04-19', 'C003', '323');

Expected Result:

campaign  |   event_date    |  
----------|-----------------|---
  C001    |   2020-01-07    |  
  C001    |   2020-01-12    |     
----------|-----------------|---
  C002    |   2020-02-15    |
  C002    |   2020-03-22    |
----------|-----------------|---
  C003    |   2020-03-18    |
  C003    |   2020-04-19    |

In the results I want to extract the newest and the second newest event_date for each campaign.
So far I am able to get the newest event_date with this query:

SELECT
campaign AS campaign,
MAX(s.event_date) AS event_date
FROM sales s
GROUP BY 1
ORDER BY 1;

How do I need to modify the query to also get the second newest event_date?

CodePudding user response：

You can use window functions to achieve this

select *
from (
  select campaign, 
         event_date, 
         sales_volume,
         dense_rank() over (partition by campaign order by event_date desc) as rnk
  from sales
) t
where rnk <= 2

Online example