How to query the same categories AVG value of recently updated date in postgres?

Question

Is that possible to do like below in a single query in Postgres?

Table: name

-------------------------------------
| id    | unique_name   | category  |
-------------------------------------
| 1     | A             | One       |
| 2     | B             | One       |
| 3     | C             | One       |
| 4     | D             | Two       |
| 5     | E             | Two       |
| 6     | F             | Two       |
| 7     | G             | Three     |
| 8     | H             | Three     |
-------------------------------------

Table: price

-----------------------------------------------------
| id    | name_id       | amount    | updated_date  |
-----------------------------------------------------
| 1     | 1             | 4.3       | 20-06-2020    |
| 2     | 2             | 2.3       | 20-06-2020    |
| 3     | 2             | 2.4       | 18-06-2020    |
| 4     | 3             | 4.4       | 20-06-2020    |
| 5     | 3             | 6.3       | 15-06-2020    |
| 6     | 4             | 0.2       | 10-06-2020    |
| 7     | 4             | 0.3       | 15-06-2020    |
| 8     | 4             | 7.4       | 20-06-2020    |
| 9     | 5             | 3.4       | 20-06-2020    |
-----------------------------------------------------

I have a table structure like above.

I want avg amount of the same category which is updated recently.

Something like below:

-----------------------------
| avg           | category  |
-----------------------------
| 3.66          | One       |     (4.3 + 2.3 + 4.4) / 3
| 5.4           | Two       |     (7.4 + 3.4) / 2
-----------------------------

Answer 1

You can use distinct on to get the latest price row per name_id, then join the name table and aggregate:

select n.category, avg(p.price) avg_price
from name n
inner join (
    select distinct on(name_id) p.*
    from price p
    order by name_id, updated_at desc
) p on p.name_id = n.id
group by n.category