I have below table

I want to transform my data to see in one row what the price customers pay in this year, i.e. 2022, and last year, i.e. 2021, and calculate its delta. If a customer did not have a transaction in 2022, I want to exclude them from my output.

So the result should look like:

I am thinking I need to self join and use a window function, but not sure how to approach it?

CodePudding user response：

You may use LAG function with a CTE as the following:

With CTE As
(
  Select Date_ As CY, Price As Price_CY,
       Lag(Date_) Over (Partition By Customer, Product Order By Date_) As PY,
       Lag(Price) Over (Partition By Customer, Product Order By Date_) As Price_PY,
       Customer, Product,
       Price / Lag(Price) Over (Partition By Customer, Product Order By Date_) As Price_Delta
  From Your_Table
)
Select CY, Price_CY, PY, Price_PY, Customer, Product, Price_Delta
From CTE
Where CY = 2022 And Customer = 1 And Product = 'A'

See a demo from uk<>fiddle.

CodePudding user response：

You can use Conditional Aggregation along with ROW_NUMBER() Analytic function while matching the "Date" columns values with the values of the last two years such as

WITH  t2 AS
(
  SELECT column_value AS year, 
         MAX(CASE WHEN "Date"=column_value THEN price END) AS price, 
         Customer,Product,
         ROW_NUMBER() 
         OVER (PARTITION BY Customer,Product ORDER BY column_value DESC) AS rn 
    FROM table(sys.odcinumberlist(TO_CHAR(sysdate,'yyyy')-1,TO_CHAR(sysdate,'yyyy')))
   CROSS JOIN t
   GROUP BY column_value, Customer,Product      
)
SELECT MAX(CASE WHEN rn = 1 THEN year END) AS cy,
       MAX(CASE WHEN rn = 1 THEN price END) AS price_cy,
       MAX(CASE WHEN rn = 2 THEN year END) AS py,
       MAX(CASE WHEN rn = 2 THEN price END) AS price_py,
       Customer,Product,
       MAX(CASE WHEN rn = 1 THEN price END)/
       MAX(CASE WHEN rn = 2 THEN price END) AS price_delta
  FROM t2
 GROUP BY Customer,Product    
HAVING NVL(MAX(CASE WHEN rn = 1 THEN price END),0)*
       NVL(MAX(CASE WHEN rn = 2 THEN price END),0)>0 

Demo

CodePudding user response：

If you want current year and previous year relative to the current date (not for each input year), then you may filter input by two years and then pivot it:

select
  cy, cy_price,
  py, py_price,
  cy_price/py_price as delta,
  customer_id,
  product
from (
  select *
  from test_tab
  --reduce input
  where dt in (
    extract(year from sysdate),
    extract(year from sysdate) - 1
  )
)
pivot(
  max(price) as price,
  max(dt)
  for dt in (
    2022 as cy,
    2021 as py
  )
)
where cy is not null
  --there's something for this year

CY	CY_PRICE	PY	PY_PRICE	DELTA	CUSTOMER_ID	PRODUCT
2022	110	2021	100	1.1	1	A

db<>fiddle here