Pivot in a combined table

Database
WL
N/A

Pivot in a combined table

Hi,

 

I would like to get the number of new customers yesterday for each market from a combined table.

Table FD_Player_Acquisition contains player id and a first time customer column.

  • 1 in New_Registration_Qty denote new customer and 0 denotes existing customers

 

Table Market contains the market id and market name.

The joint key is market id.

 

Here is what I would like to achieve 

 

1.

Market name

New customer

USA

100

UK

90

 

2.

USA

UK

100

90

 

Here is the code I wrote.

 

SELECT COUNT(Player_Id)

FROM FD_Player_Acquisition
JOIN Market 
ON (FD_Player_Acquisition.marketId = Market.marketId)
WHERE FD_Player_Acquisition.Summary_Date = DATE - 1 
AND Market.marketId IN(123, 234)
AND New_Registration_Qty = 1

 

I found that SELECT COUNT(Player_Id), market.marketname is not working and Pivot is not available in Teradata.

 

Would you please let me know how I can generate those 2 tables?

 

Thanks

 

 

 

 

1 REPLY

Re: Pivot in a combined table

Hope this helps Smiley Happy, I've added only simple filters, you can add on top of below as per your requirement.

 

SELECT MKT.MARKETNAME ,COUNT(FD.PLAYER_ID)
FROM FD_PLAYER_ACQUISITION FD
JOIN MARKET MKT
ON (FD.MARKET_ID = MKT.MARKET_ID)
AND NEW_REGISTRATION_QTY = 1
GROUP BY MKT.MARKETNAME;

SELECT SUM(CASE WHEN MKT.MARKETNAME='USA' THEN 1 ELSE 0 END) AS USA,
SUM(CASE WHEN MKT.MARKETNAME='UK' THEN 1 ELSE 0 END) AS UK
FROM FD_PLAYER_ACQUISITION FD
JOIN MARKET MKT
ON (FD.MARKET_ID = MKT.MARKET_ID)
AND NEW_REGISTRATION_QTY = 1;

Wiki