'SQL query based on datediff

I am looking to construct a query using the ad exposure and conversion logs, I want to identify the ad exposure that gets "last touch" credit for the trial start. The conversion should have happened within 7 days after the ad exposure. Where Last credit means it was the last impression or click prior to conversion

This are my tables

ad_exposure_and_conversion

user_id ad_id Exposure Time event_type
12368 574012 9/5/20 12:37 AM Click
12368 574012 9/12/20 6:02 PM Impression
12368 574012 9/13/20 5:53 AM Impression
13953 449785 9/14/20 10:30 PM Impression
17000 96336 9/1/20 11:29 AM Impression
17000 96336 9/7/20 8:10 AM Impression
17000 96336 9/10/20 9:43 PM Impression
17000 96336 9/12/20 1:36 AM Impression
22362 449785 9/2/20 5:41 AM Impression
22362 449785 9/4/20 3:31 PM Impression
22362 449785 9/4/20 6:05 PM Impression
22362 449785 9/5/20 6:22 PM Impression
22362 449785 9/7/20 3:48 PM Impression
22362 449785 9/8/20 12:04 AM Impression
22362 449785 9/11/20 6:00 PM Impression
22362 449785 9/12/20 6:43 PM Impression
22362 449785 9/14/20 6:05 AM Impression
23437 96336 9/1/20 8:13 AM Impression
23437 96336 9/2/20 6:14 PM Impression
23437 96336 9/3/20 3:23 PM Impression
23437 96336 9/3/20 10:06 PM Impression
23437 96336 9/3/20 11:53 PM Impression
23437 96336 9/4/20 8:07 AM Impression
23437 96336 9/6/20 5:06 PM Click
23437 96336 9/8/20 9:17 AM Impression
23437 96336 9/8/20 10:32 AM Impression
23437 96336 9/8/20 12:51 PM Impression
23437 96336 9/9/20 1:30 AM Impression
23437 96336 9/10/20 9:49 PM Impression
23437 96336 9/12/20 6:27 PM Impression
23437 96336 9/13/20 6:22 AM Impression
23437 96336 9/14/20 3:39 AM Impression

I dont quite understand the last credit part as I dont have ad experience. But this is what I have done so far

select ad_id
from ad_exposure_and_conversion
where DATEDIFF(DAY,a.Exposure Time,GETDATE() )>7 

Can someone share your thoughts



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source