Select last order date, all the order id and user id for particular seller in postgreSQL-CodePudding

I am new to postgreSQL

Updated Initial Question:

I have two tables orders & users

user_id	username
a	user1
b	user2
c	user3

order_id	ordered_at	user_id	seller_id
1	2022-08-10	a	s1
2	2022-08-09	b	s1
3	2022-07-06	a	s2
4	2022-08-01	a	s1
5	2022-05-02	c	s1
6	2022-08-11	b	s2
7	2022-08-12	b	s1

My postgres SQL query should give me the result for seller s1:

order_id	last_purchase	user_id	username
1	2022-08-10	a	user1
4	2022-08-10	a	user1
2	2022-08-12	b	user2
7	2022-08-12	b	user2
5	2022-05-02	c	user3

For this I have wrote the SQL query:

        SELECT
            MAX(orders.ordered_at) AS last_purchase,
            orders.order_id AS order_id,
            users.user_id AS users_id,
            users.username as username
        FROM
            orders
        INNER JOIN
            users
        ON
            orders.user_id = users.user_id
        WHERE 
            orders.seller_id='s1'
        GROUP BY users.user_id;

I am not getting the desired output

Any suggestions or help is welcomed. Thank you!

CodePudding user response：

if I am understanding your question you want last purchase to be the max date per user id. if so use a windowing function

 SELECT
            MAX(orders.ordered_at) over
             (partition by users.users_id  order by orders.ordered_at desc) as 
             last_purchase,
            orders.order_id AS order_id,
            users.users_id AS users_id,
            users.username as username
        FROM
            orders
        INNER JOIN
            users
        ON
            orders.user_id = users.id

CodePudding user response：

All the attributes in the SELECT except inside the MAX must be part of the GROUP BY clause.

SELECT
        MAX(orders.ordered_at) AS last_purchase,
        orders.order_id AS order_id,
        users.user_id AS users_id,
        users.username as username
FROM
        orders
INNER JOIN
        users
ON
       orders.user_id = users.user_id
GROUP BY 
       users.user_id, orders.order_id, users.username;