Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE orders (
    id int,
    customer int,
    order_date date,
    order_total decimal
);

INSERT INTO orders VALUES
(1, 1, '2017-03-12', 42.23),
(2, 2, '2017-03-12', 12.54),
(3, 4, '2017-12-03', 145.89),
(4, 2, '2017-12-03', 200.00),
(5, 4, '2018-01-03', 2.90),
(6, 3, '2018-09-10', 0.98),
(7, 2, '2018-09-12', 9.99);

SELECT * FROM orders;

7 rows affected



id
customer
order_date
order_total


1
1
2017-03-12
42.23

2
2
2017-03-12
12.54

3
4
2017-12-03
145.89

4
2
2017-12-03
200.00

5
4
2018-01-03
2.90

6
3
2018-09-10
0.98

7
2
2018-09-12
9.99


-- Window functions

SELECT 
    customer, order_date, order_total
FROM (
    SELECT
        *, 
        first_value(order_date) OVER w as last_order, 
        first_value(order_total) OVER w as last_total,
        row_number() OVER w as row_count
    FROM orders
    WINDOW w AS (PARTITION BY customer ORDER BY order_date DESC)
) s
WHERE row_count = 1 AND order_date < CURRENT_DATE - 30


customer
order_date
order_total


1
2017-03-12
42.23

4
2018-01-03
2.90


-- DISTINCT ON

SELECT
    customer, order_date, order_total
FROM (
    SELECT DISTINCT ON (customer)
        *, 
        first_value(order_date) OVER w as last_order, 
        first_value(order_total) OVER w as last_total
    FROM orders
    WINDOW w AS (PARTITION BY customer ORDER BY order_date DESC)
    ORDER BY customer, order_date DESC
) s
WHERE order_date < CURRENT_DATE - 30



customer
order_date
order_total


1
2017-03-12
42.23

4
2018-01-03
2.90

id	customer	order_date	order_total
1	1	2017-03-12	42.23
2	2	2017-03-12	12.54
3	4	2017-12-03	145.89
4	2	2017-12-03	200.00
5	4	2018-01-03	2.90
6	3	2018-09-10	0.98
7	2	2018-09-12	9.99