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.
Table Market contains the market id and market name.
The joint key is market id.
Here is what I would like to achieve
Here is the code I wrote.
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?
Hope this helps , 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;