This is how my data looks like:
| Item | Year | Price |
|---|---|---|
| Hat | 2020 | 15 |
| Hat | 2021 | 17 |
| Hat | 2022 | 19 |
| Pen | 2020 | 3 |
| Pen | 2021 | 2 |
| Pen | 2022 | 3.3 |
I want to use PostgreSQL to achieve a simple data analysis task that will provide the result below:
| Item | Year | Price | Previous Year | Previous price | higher_than_previous_year |
|---|---|---|---|---|---|
| Hat | 2020 | 15 | |||
| Hat | 2021 | 17 | 2020 | 15 | yes |
| Hat | 2022 | 19 | 2021 | 17 | yes |
| Pen | 2020 | 3 | |||
| Pen | 2021 | 2 | 2020 | 3 | no |
| Pen | 2022 | 3.3 | 2021 | 2 | yes |
Previous Year: Fetches the closest year that is lesser than the current year specified in the year column. Previous Price: Fetches the price for the item that corresponds with the previous year. higher_than_previous_year: compares the price column with the previous price column and computes yes when the price is higher than the previous price and no when the previous price is higher than the year price.
I will appreciate it if any PostgreSQL guru can take a crack at this. Thanks!