How to use one column value from specific condition in current column in SQL

Question

I am trying to bring a specific column value to another column but from different row based on a condition.

Sample Dataset:

For year 2023 last_year_sales I want to use values of this_year_sales from 2022 of that specific product.

The result should look like this:

Can someone please help me with the query for this logic?

Also if possible scala-spark solution would be appreciated. Thanks in advance!

Answer 1

Assuming your database (be it MySQL or Hive) supports the LAG() analytic function, you can use that:

SELECT
    Year,
    this_year_sales,
    LAG(this_year_sales) OVER (PARTITION BY Product
                               ORDER BY Year) AS last_year_sales,
    Product
FROM yourTable
ORDER BY
    Product,
    Year;