looking for a better way to organize 'where' statements in sql-CodePudding

I'm quite new to SQL and I'm working on a problem to learn about it, but I'm a bit stuck.

Here's the task: In a CRM System there are two tables, “Contacts” (ContactID, Name, CreateDate) and “ContactActivities” (ContactID, Activity, ActivityDate). Whenever something is modified in the CRM for a contact, a new activity is added to the ContactActivities table with the ContactID of the contact and a string Activity describing the activity. Create a query which indicates for each contact whether a certain activity has happened (1) or not (0). The activity should have happened within a certain time period from the creation of the contact (take 2 weeks).

Here's what I came up with (which seems to work, by checking with SQLFiddle here):

  (SELECT SIGN(COUNT(*))
    FROM ContactActivities AS c2 
    WHERE c2.Activity = 'opt-in'
      AND c2.ContactID = c1.ContactID
      AND (SELECT MIN(c2.ActivityDate) - c1.CreationDate) < 14
  ) AS OPT_IN,
  (SELECT SIGN(COUNT(*))
    FROM ContactActivities AS c2 
    WHERE c2.Activity = 'purchase'
      AND c2.ContactID = c1.ContactID
      AND (SELECT MIN(c2.ActivityDate) - c1.CreationDate) < 14
  ) AS PURCHASE,
  (SELECT SIGN(COUNT(*))
    FROM ContactActivities AS c2 
    WHERE c2.Activity = 'deleted'
      AND c2.ContactID = c1.ContactID
      AND (SELECT MIN(c2.ActivityDate) - c1.CreationDate) < 14
  ) AS DELETED
FROM Contacts as c1

Now I'm wondering (and I'm quite sure actually) that this can be done with some better nesting of the WHERE statements - but I don't really know how.

I'm happy about any help!

CodePudding user response：

Like this, using a join and aggregation:

SELECT c1.ContactID
     , MAX(CASE WHEN c2.Activity = 'opt-in'   THEN 1 ELSE 0 END) AS OPT_IN
     , MAX(CASE WHEN c2.Activity = 'purchase' THEN 1 ELSE 0 END) AS PURCHASE
     , MAX(CASE WHEN c2.Activity = 'deleted'  THEN 1 ELSE 0 END) AS DELETED
  FROM      Contacts          AS c1
  LEFT JOIN ContactActivities AS c2
    ON c2.ContactID = c1.ContactID
   AND c2.ActivityDate - c1.CreationDate < 14
 GROUP BY c1.ContactID
;

Updated fiddle

CodePudding user response：

would check for a positive sum for condition case when then else end, by contactid:

select c.CONTACTID
  , sum(case when ACTIVITY='opt-in'   and datediff(ACTIVITYDATE, CREATIONDATE)<=14 then 1 else 0 end) > 0 as OPT_IN
  , sum(case when ACTIVITY='purchase' and datediff(ACTIVITYDATE, CREATIONDATE)<=14 then 1 else 0 end) > 0 as PURCHASE
  , sum(case when ACTIVITY='deleted'  and datediff(ACTIVITYDATE, CREATIONDATE)<=14 then 1 else 0 end) > 0 as DELETED
from contacts c
left join contactActivities a
on a.CONTACTID = c.CONTACTID
group by c.CONTACTID
;

SQL Fiddle here