SQL query to find the closest possible date (less than or equal to) and match the data accordingly

user_17435878 0
Viewed 243

Map the record in the Branch1 table with records in the Email1 table and show the value for visits in the Email1 table as explained below:

Requirement:

  1. the value of 'visits ' for the 'campaign_name' 0001_RegOnly with 'day_id' 7th Nov 2021 in the branch1 table should be matched to the record in the email1 table which has the same campaign_name and has a send_date that is closest possible (less than or equal to) date to the day_id in the branch1 table. So, there are 3 records with the same campaign_name (0001_RegOnly) in the email table with dates 1st, 5th and 6th of Nov. So, the value of visits should be inserted to 5th nov record in the email table.

a) Incase there were records in the email table for the same campaign_name (0001_RegOnly), let's say for the date 8th of November instead of 6th of November, then the value should be assigned to the record with the same campaign name that has the date as 5th of November. And not 8th of Nov as 8th of Nov is greater than 7th of Nov.

b) Incase there were records in the email table for the same campaign name (0001_RegOnly), let's say for the date 7thof November along with 1st, 5th, and 6th of November, then the value should be assigned to the record with the same campaign name that has the date as 7th of November as it is the closest possible date, in this case equal (branch1.day_id is 7 and email1.send_date is 7 as well)

Condition:

  1. branch1.campaign_name = email1.campaign_name
  2. branch1.day_id >= email1.send_date

Tables:

EMAIL1 Table

Branch1 Table

Expected Output

SQL QUERY that I have used:

with CTE as
(
select  campaign_name, send_date, lead (send_date) over (order by send_date) as nextdate
  from email1
  group by campaign_name, send_date
)
select a.campaign_name, a.send_date, sum(b.visits) as visits
from CTE a  left join branch1 b 
on b.day_id>a.send_date and b.day_id<a.nextdate
and a.campaign_name = b.campaign_name
group by a.send_date, a.campaign_name
order by send_date
;

Actual Output: SQL Query Output

snowflake-cloud-data-platform sql
2 Answers

This will give you what you're looking for.

--get all emails that meet <= the send date 
with all_emails_lt_branch_date as (
select b.campaign_name, 
b.day_id,
b.visits,
e.send_date 
case when e.send_date <= b.day_id then true else false end as possible 
from branch1 b 
left join email1 e  on e.campaign_name = b.campaign_name
where possible = true 
)

--keep only the latest / max email date for each day_id 
 select * , 
row_number() over (partition by campaign_name, day_id order by  send_date) as row_number
from all_emails_lt_branch_date
qualify row_number = 1
user_164135120

With 2 CTE's to be the fake data tables

WITH data_email(campaign_name, send_date, visits) AS (
    SELECT column1, column2::date, null FROM VALUES
    ('0001_RegOnly', '2021-11-01'),
    ('0001_RegOnly', '2021-11-05'),
    ('0001_RegOnly', '2021-11-06'),
    ('002_RegOnly', '2021-09-28'),
    ('002_RegOnly', '2021-10-30'),
    ('002_RegOnly', '2021-11-01'),
    ('002_RegOnly', '2021-11-03')
), data_branch(campaign_name, day_id, visits) AS (
    SELECT column1, column2::date, column3 FROM VALUES
    ('0001_RegOnly', '2021-11-04', 5),
    ('0001_RegOnly', '2021-11-07', 1),
    ('002_RegOnly', '2021-10-15', 6),
    ('002_RegOnly', '2021-11-02', 8)
)

then the SQL to use that data:

WITH email_cte AS (
    SELECT e.*, 
        lead(e.send_date,1,to_date('2100-01-01')) over (partition by e.campaign_name order by e.send_date) as next_sent
    FROM data_email AS e
)
SELECT e.campaign_name, 
     e.send_date,
     b.visits
FROM  email_cte AS e
LEFT JOIN data_branch AS b
    ON b.campaign_name = e.campaign_name 
        AND b.day_id between e.send_date and next_sent 
        AND b.day_id < e.next_sent
--QUALIFY row_number() over (partition by e.campaign_name, e.send_date order by b.day_id ) = 1
ORDER BY 1,2;

we get, the wanted outcomes.

CAMPAIGN_NAME SEND_DATE VISITS
0001_RegOnly 2021-11-01 5
0001_RegOnly 2021-11-05
0001_RegOnly 2021-11-06 1
002_RegOnly 2021-09-28 6
002_RegOnly 2021-10-30
002_RegOnly 2021-11-01 8
002_RegOnly 2021-11-03

So this works by using a CTE to calculate the next send time, with a "far future" value injected so we can "just use the value" then we use a BETWEEN to speed-up the range checking on the date ranges, but given a BETWEEN includes the end value, and next_sent is a known "other email sent" we don't want to double match, so we add an extra clause to drop the inclusion.

And now that I go to explain the QUALIFY I don't think it's needed. So I commented that out.

But this is all assuming the and email yesterday, is 100% the reason I visited today, verse last weeks email, plus when I was free to action the response..

user_439920
Related