Schema:
I have a table with inbound calls. I'm trying to track their activity down the funnel.
If someone starts an application that data will be in the application table.
If someone starts an application, they will also have an opportunity (which also houses approvals and whether or not they became a customer).
Now for this example, we are reaching out to existing customers who have one product, but We are trying to figure out if they then got another product after the date of an email campaign which was aimed at customers who have one product and we are promoting another (we can use the phone call date as a filter as the call in number is unique to the email campaign).
THE PROBLEM IS if they took the second product, THEY CAN HAVE MORE THAN OPPORTUNITY ID (A CUSTOMER WILL ALREADY HAVE AN OPPID, BUT WILL ONLY HAVE ONE ACCOUNTID (ACCOUNT ID IS THE ONLY WAY TO COUNT DISTINCT) SO BASICALLY I NEED TO FIND A WAY TO PULL THE MOST RECENT O.CREATEDDATE AND DETERMINE IF THAT DATE >= C.PHONECALLINDATE
Would the max function be appropriate here? I can explain in much more detail...really hitting a wall here though.
SELECT
c.phonenumberiwanttotrack
,COUNT(distinct case when a.applicationcreateddate >= c.phonecalldate THEN a2.id else null end)
from
calltable c
LEFT JOIN applicationtable a ON a.applicationphonenumber = c.applicationphonenumber
LEFT JOIN opportunitytable o ON o.oppid = a.oppid
left join account table a2 ON o.accountid= a2.id
where
c.callersnumber IN ('888888888', '33333333')
AND c.dateofcall >= '5-18-2015'