Count of past instances in an appended table

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.

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