How to find the first and last date prior to a particular date in Postgresql?

user_11661628 0
Viewed 335

I am a SQL beginner. I have trouble on finding the answer of this question

For each customer_id who made an order on January 1, 2006, what was their historical (prior to January 1, 2006) first and last order dates?

I've tried to solve it using a subquery. But I don't know how to find the first and last order dates prior to Jan 1.

Columns of table A: 

customer_id            
order_id              
order_date   
revenue  
product_id

Columns of table B: 

product_id  
category_id

SELECT customer_id, order_date FROM A
(
     SELECT customer_id FROM A
     WHERE order_date = ‘2006-01-01’
)
WHERE ...
date postgresql
2 Answers

You can use conditionals in aggregate functions:

SELECT customer_id, MIN(order_date) AS first, MAX(order_date) AS last FROM A 
WHERE customer_id IN (SELECT customer_id FROM A WHERE order_date = ‘2006-01-01’) AND order_date < '2006-01-01'
GROUP BY customer_id;
user_23523440

There are two subqueries actually. First for "For each customer_id who made an order on January 1, 2006" and second for "their historical (prior to January 1, 2006) first and last order dates"

So, first:

select customer_id from A where order_date = '2006-01-01';

and second:

select customer_id, min(order_date) as first_date, max(order_date) as last_date
from A
where order_date < '2006-01-01' group by customer_id;

Finally you need to get only those customers from second subquery who exists in the first one:

select customer_id, min(order_date) as first_date, max(order_date) as last_date
from A as t1
where 
    order_date < '2006-01-01' and
    customer_id in (
        select customer_id from A where order_date = '2006-01-01')
group by customer_id;

or, could be more efficient:

select customer_id, min(order_date) as first_date, max(order_date) as last_date
from A as t1
where 
    order_date < '2006-01-01' and
    exists (
        select 1 from A as t2
        where t1.customer_id = t2.customer_id and t2.order_date = '2006-01-01')
group by customer_id;
user_5931440
Related