This is my desired output:
CampaignName CampaignDate UsersTargeted CountOfUsersBought 2x1 2018-11-24 1 (UserId 2) 1 (UserId 2) 3x2 2018-11-25 2 (Both) 1 (UserId 1)
‘CountOfUsersBought’ I want it to be Amongst All Targeted Users.
And the Table (Updated Daily) from where I get the data to fill the previous output has the following structure:
UserId EligibleForOffer(0,1) BoughtToday(0,1) Timestamp 1 0 0 2018-11-24 1 1 0 2018-11-25 1 1 1 2018-11-26 1 0 0 2018-11-27 2 1 0 2018-11-24 2 1 1 2018-11-25 2 1 0 2018-11-26 2 0 1 2018-11-27
I want to store on the variable ‘CountOfUsersBought’ the count of all the users that actually bought, not only today but all time. How would you go about doing this query?
Please note that users also buy without an offer, so I only want to count the past instances WHERE EligibleForOffer = 1 AND BoughtToday = 1 AND Timestamp <= ‘CampaignDate’+ 1 day
I know for another table the users that are targeted for each campaign, I just want to keep for more than ‘today’ the count of users that took the offer that was given to them.
Advertisement
Answer
You can GROUP BY
dates and use SUM
the find how many users eligible for campaigns and use CASE
to do your conditions. And bonus is MIN
to find which specific user related with the condition is only one user match.
CREATE TABLE Campain ( UserId INT ,EligibleForOffer BIT ,BoughtToday BIT ,Timestamp DATE ) INSERT INTO Campain VALUES (1,0,0,'2018-11-24') ,(1,1,0,'2018-11-25') ,(1,1,1,'2018-11-26') ,(1,0,0,'2018-11-27') ,(2,1,0,'2018-11-24') ,(2,1,1,'2018-11-25') ,(2,1,0,'2018-11-26') ,(2,0,1,'2018-11-27') SELECT Timestamp ,SUM(CAST(EligibleForOffer AS INT)) NumberOfUsersTargeted ,CASE WHEN SUM(CAST(EligibleForOffer AS INT))=1 THEN 'UserId-'+CAST(MIN(UserId) AS VARCHAR) WHEN SUM(CAST(EligibleForOffer AS INT))>1 THEN 'Multiple Users(Both)' ELSE 'No Target' END UsersTargetedDetail ,SUM(CAST(BoughtToday AS INT)) NumberOfBought ,CASE WHEN SUM(CAST(BoughtToday AS INT))=1 THEN 'UserId-'+CAST(MIN(UserId) AS VARCHAR) WHEN SUM(CAST(BoughtToday AS INT))>1 THEN 'Multiple Users(Both)' ELSE 'No Buying' END BoughtDetail FROM Campain GROUP BY Timestamp