I have two table with columns as mentioned below:

**CUSTOMER** table as **DIM\_CUSTOMER**:

```
ID_CUSTOMER, CUSTOMER_NAME

```

**TRANSACTION** table as **FACT\_TRANSACTION**:

```
ID_CUSTOMER, DATE, TOTAL_PRICE, QUANTITY

```

Problem statement is to 

*Find top 100 customers and their average spend, average quantity by each year. Also find the percentage of change in their spend.*

My approach:

```
SELECT TOP 100 
    YEAR(FT.DATE) AS [YEAR],
    FT.ID_CUSTOMER AS [CUSTOMER NAME],
    FT.TOTAL_PRICE AS [TOTAL AMT],
    AVG(FT.TOTAL_PRICE) AS [AVG SPEND],
    AVG(FT.QUANTITY) AS [AVG QUANTITY]
FROM 
    FACT_TRANSACTIONS FT
INNER JOIN 
    DIM_CUSTOMER DC ON FT.ID_CUSTOMER = DC.ID_CUSTOMER
GROUP BY 
    FT.DATE, FT.ID_CUSTOMER, FT.TOTAL_PRICE
ORDER BY 
    3 DESC 

```

This is resulting in the top 100 customers based on their usage.

Now I need to determine the percentage change in their spend YEAR wise.

How can I do that? Probably using PIVOT option herein will help, but I'm unsure.