CODEWITHSUNDEEP
sql-servert-sqlpivot
gm-123
gm-123

Reputation: 238

How to use Pivot in SQL Server?

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.

Upvotes: 0

Views: 666

Answers (2)

Eralper
Eralper

Reputation: 6612

Can you please try following SQL CTE query ?

;with topcustomers as(
    SELECT distinct top 100
        ID_CUSTOMER,
        SUM(TOTAL_PRICE) over (partition by ID_CUSTOMER) as TotalSPEND
    FROM FACT_TRANSACTION
    order by TotalSPEND desc
), cte as (
    SELECT
        distinct
        t.ID_CUSTOMER, YEAR(t.DATE) [YEAR], TotalSPEND,
        AVG(t.QUANTITY * 1.0) over (partition by t.ID_CUSTOMER, YEAR(t.DATE)) as AverageQUANTITY,
        AVG(t.TOTAL_PRICE * 1.0) over (partition by t.ID_CUSTOMER, YEAR(t.DATE)) as AverageSPEND
    FROM FACT_TRANSACTION t
    INNER JOIN topcustomers c on c.ID_CUSTOMER = t.ID_CUSTOMER
)
select 
    *,
    ( AverageSPEND - lag(AverageSPEND,1) over (partition by ID_CUSTOMER order by [YEAR]) ) * 100.0 / AverageSPEND as [%Change]
from cte

Upvotes: 0

gotqn
gotqn

Reputation: 43636

You can try using LAG in order to access the previous [AVG SPEND] for the current row. The idea is to group the data for each [CUSTOMER NAME] using PARTITION BY and then to order the data by the [YEAR]. The function will give us the previous result and we can calculated easily the difference.

Try something like this:

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]
INTO #DataSource
FROM 
    FACT_TRANSACTIONS FT
INNER JOIN 
    DIM_CUSTOMER DC ON FT.ID_CUSTOMER = DC.ID_CUSTOMER
GROUP BY 
    YEAR(FT.DATE), FT.ID_CUSTOMER, FT.TOTAL_PRICE
ORDER BY 
    [AVG SPEND] DESC 

SELECT *
      ,[AVG SPEND] - LAG([AVG SPEND], 1, 0) OVER (PARTITION BY [CUSTOMER NAME] ORDER BY [YEAR])
FROM #DataSource

Note, that:

  • the function requires SQL Server 2012+
  • you can change the partitioning and ordering as you like in order to satisfy your real goal (for example you can use ORDER BY [YEAR] DESC
  • you can use the LEAD function in order to access the next value within the group if you want to calculated difference in advace
  • I materialized the data in temporary table, but you can use table variable or whatever you are using

Upvotes: 1

Related Questions