Add the last appearance to table

I have two tables (for example)
table1 will contain stores, there are 2 types ‘candy store’ and ‘dental store’. Each row contains information about a customer’s purchase in a particular store

table1 ( purchases ):

+----+---------+------------------+-------+
| id | store   | date of purchase | money |
| 1  | store 1 | 2016-01-01       | 10    |
| 1  | store 5 | 2018-01-01       | 50    |
| 2  | store 2 | 2017-01-20       | 10    |
| 2  | store 3 | 2019-02-20       | 15    |
| 3  | store 2 | 2017-02-02       | 20    |
| 3  | store 6 | 2019-01-01       | 60    |
| 1  | store 1 | 2015-01-01       | 20    |
+----+---------+------------------+-------+

table2 ( type ):

+---------+--------+
| store   | type   |
| store 1 | candy  |
| store 2 | candy  |
| store 3 | candy  |
| store 4 | dental |
| store 5 | dental |
| store 6 | dental |
+---------+--------+

I want my query to return a table like this:

+----+---------------+-----------------+---------------+
| id | the last place| the last date c |the last date d|
| 1  | store 5       | 2016-01-01      |  2018-01-01   |
| 2  | store 3       | 2019-02-20      |  -            |
| 3  | store 6       | 2017-02-02      |  2019-01-01   |
+----+---------------+-----------------+---------------+

where

[the last place] is the last store where client bought smth;

[the last date c] – the last date of buying in candy’s shop

[the last date d] is the last date of buying in dental shop

and delete rows if no any matches(all values are null)

Answer

You can use conditional aggregation and join:

select t1.id,
       (array_agg(store order by t1.date_of_purchase desc))[1] as last_store,
       max(t1.date_of_purchase) filter (where t2.type = 'candy') as last_candy,
       max(t1.date_of_purchase) filter (where t2.type = 'dental') as last_dental
from table1 t1 join
     table2 t2
     using (store)
group by t1.id;

Advertisement

Answer