SQL: get latest (un)subscribeaction from table

Question

I have the following table:

ID (int)
EMAIL (varchar(50))
CAMPAIGNID (int)
isSubscribe (bit)
isActionByUser (bit)

This table stores all subscribe and unsubscribe actions on campaigns for a user. These actions can be done by the user itself (isActionByUser = true) or by the administration desk (isActionByUser = false).

I need to get the last action to determine if a user is subscribed or unsubscribed. But keeping in mind that when a user did an unsubscribe action from a campaign, it will have priority on other subscribe actions by the administration desk.

I have found a nice solution to get the lastest record grouped by EMAIL and CAMPAIGNID. But I can't figure out how I do incorporate the requirement that an isActionByUser = true, has absolute priority over records with isActionByUser = false. Also: when the administration desk does an unsubscribe action, it will have priority over a record with (isSubscribe = true and isActionByUser).

Example data:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
1     a@aa.com    1             1                0
2     b@bb.com    1             1                0
3     c@cc.com    1             1                0
4     a@aa.com    1             0                1
5     a@aa.com    1             1                0
6     c@cc.com    1             1                1
7     c@cc.com    1             0                0

The expected result would be:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
2     b@bb.com    1             1                0   
4     a@aa.com    1             0                1
7     c@cc.com    1             0                0

With the following query

select cs1.*
from 
    [TABLE] cs1 
    left join 
    [TABLE] cs2
    on 
    cs1.EM_EMAIL = cs2.EM_EMAIL
    and 
    cs1.EM_CAMPAIGNID = cs2.EM_CAMPAIGNID
    and 
    cs1.id < cs2.id
where cs2.id is null

I' m having the following result:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
2     b@bb.com    1             1                0
5     a@aa.com    1             1                0
7     c@cc.com    1             0                0

Another approach:

SELECT *
FROM [TABLE] cs
WHERE id in 
  (
    SELECT top 1 id 
    FROM [TABLE] ss
    WHERE 
        cs.EMAIL = ss.EMAIL
        and 
        cs.CAMPAIGNID = ss.CAMPAIGNID 
        and ISSUBSCRIBE = (
            select top 1 min(convert(int, ISSUBSCRIBE)) 
            FROM [TABLE] sss
            WHERE 
                cs.EMAIL = sss.EMAIL
                and 
                cs.CAMPAIGNID = sss.CAMPAIGNID
            )
       and ISACTIONBYUSER= (
            select top 1 max(convert(int, ISACTIONBYUSER)) 
            FROM [TABLE] ssss
            WHERE 
                cs.EMAIL = ssss.EMAIL
                and 
                cs.CAMPAIGNID = ssss.CAMPAIGNID
            )
        )   

This will produce the following result:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
2     b@bb.com    1             1                0
4     a@aa.com    1             0                1
6     c@cc.com    1             1                1

Which is also not correct. And I'm afraid performance will be a big rpoblem with this approach.

So any ideas how I can solve this?

Answer 1

Ok, try the following query:

SELECT DISTINCT B.*
FROM YourTable A
OUTER APPLY (SELECT TOP 1 *
             FROM YourTable
             WHERE Email = A.Email AND CampaignId = A.CampaignId
             ORDER BY CASE WHEN ISSUBSCRIBE = 0 THEN 1 ELSE 2 END,
             CASE WHEN ISACTIONBYUSER = 1 THEN 1 ELSE 2 END,
             ID DESC) B

Answer 2

Try this: [Updated to handle unsubscribe and subscribed users]

    declare @test table (id int, email varchar(100), CAMPAIGNID int, ISSUBSCRIBE bit, ISACTIONBYUSER bit)
INSERT INTO @test 
SELECT 1,'a@aa.com',1,1,0 UNION 
SELECT 2,'b@bb.com',1,1,0 UNION 
SELECT 3,'c@cc.com',1,1,0 UNION 
SELECT 4,'a@aa.com',1,0,1 UNION 
SELECT 5,'a@aa.com',1,1,0 UNION 
SELECT 6,'c@cc.com',1,1,1 UNION 
SELECT 7,'c@cc.com',1,0,0 UNION
select 8, 'd@dd.com', 1, 1, 1 UNION 
select 9, 'd@dd.com', 1, 0, 1 UNION 
select 10, 'd@dd.com', 1, 1, 1


;WITh CTE AS
(
    select s.*, 
    ROW_NUMBER() OVER (PARTITION BY email,campaignid
    ORDER BY 
    case 
        when ISSUBSCRIBE = 0 AND ISACTIONBYUSER = 0 THEN 1 
        when ISSUBSCRIBE = 0 AND ISACTIONBYUSER = 1 THEN 1 
        when ISSUBSCRIBE = 1 AND ISACTIONBYUSER = 1 THEN 1 ELSE 2 END, ID DESC) Rn1
    from @test s
)
SELECT * FROM CTE WHERE Rn1 = 1
order by id

Answer 3

This is some standard SQL that might get you there, though it's not the prettiest ever:

Updated:

select s.*
from Subscriptions s
    join (
        -- Apply the user unsubscribe logic to get the proper ID
        select case when b.ID is not null and a.ISACTIONBYUSER = 0 then b.ID else a.ID end as ID
        from (
                -- Latest overall
                select ID, EMAIL, CAMPAIGNID,
                    (select ISACTIONBYUSER from Subscriptions where ID = z.ID) as ISACTIONBYUSER
                from (
                    select max(ID) as ID, EMAIL, CAMPAIGNID
                    from Subscriptions a
                    group by EMAIL, CAMPAIGNID
                ) as z
            ) as a
            left join (
                -- Latest user unsubscribe
                select max(ID) as ID, EMAIL, CAMPAIGNID, 1 as ISACTIONBYUSER
                from Subscriptions
                where ISSUBSCRIBE = 0
                    and ISACTIONBYUSER = 1
                group by EMAIL, CAMPAIGNID
            ) as b on a.EMAIL = b.EMAIL
                and a.CAMPAIGNID = b.CAMPAIGNID
    ) as i on s.ID = i.ID

I've updated this to account for this case:

insert into Subscriptions select 8, 'd@dd.com', 1, 1, 1
insert into Subscriptions select 9, 'd@dd.com', 1, 0, 1
insert into Subscriptions select 10, 'd@dd.com', 1, 1, 1