There is big table with details on customers, bought items and date of the transaction.
I need a result which contains the name of the client and the last 3 items that were bought in columns last_item, second_item and third_item.
| customer | item | date |
|---|---|---|
| 1aa | A | 2023-02-02 10:02:00 |
| 1aa | B | 2023-02-02 10:01:00 |
| 1aa | B | 2023-02-02 10:00:00 |
| 1aa | A | 2023-02-02 09:01:00 |
| 1aa | D | 2023-02-02 09:00:00 |
| 1aa | C | 2023-02-02 08:00:00 |
| 2aa | A | 2023-02-02 10:02:00 |
| 2aa | D | 2023-02-02 10:01:00 |
| 3aa | C | 2023-02-02 10:00:00 |
The result should be the following:
| customer | last_item | second_item | third_item |
|---|---|---|---|
| 1aa | A | B | D |
| 2aa | A | D | |
| 3aa | C |
I use
ROW_NUMBER() OVER(PARTITION BY customer,item ORDER BY date DESC) as item_ranking
And them use the results in CTE query with CASEs, but I believe there should be better and more clean way to do this.