SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
	Jul 25 2020 11:26:55 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table PAGE_VIEWS (Event_Day date,	Member_ID BIGINT,	CV1 varchar(20),	CV2 varchar(20),	CV3 varchar(20),	CV4 varchar(20));
insert into page_views values('11/05/2021',	183300,	'APP',	'camp1',	'Trigger',	'APP-camp1-Trigger');
insert into page_views values('11/14/2021',	183300,	'SEARCH',	'camp4',	'Search',	'SEARCH-camp4-Search');
insert into page_views values('11/04/2021',	193320,	'SEARCH',	'camp5',	'Search',	'SEARCH-camp5-Search');
insert into page_views values('11/08/2021',	193320,	'SEARCH9',	'camp5',	'Search',	'SEARCH-camp5-Search');
insert into page_views values('11/06/2021',	193320,	'SEARCH2',	'camp6',	'Search',	'SEARCH-camp6-Search');
insert into page_views values('11/07/2021',	193320,	'SEARCH1',	'camp7',	'Search',	'SEARCH-camp7-Search');
insert into page_views values('11/10/2021',	141542,	'SEARCH1',	'camp7',	'Search',	'SEARCH-camp7-Search');
7 rows affected

create table orders(Date date,  Purchase_Order_ID varchar(20), Membership_Number BIGINT, Order_Value float);
insert into orders values('12/07/2021', '0088',	183300,	29.34);
insert into orders values('12/18/2021', '0180',	193320,	132.51);
insert into orders values('12/04/2021', '0050',	141542,	24.35);
3 rows affected

select * from page_views;
select * from orders;


Event_Day
Member_ID
CV1
CV2
CV3
CV4


2021-11-05
183300
APP
camp1
Trigger
APP-camp1-Trigger

2021-11-14
183300
SEARCH
camp4
Search
SEARCH-camp4-Search

2021-11-04
193320
SEARCH
camp5
Search
SEARCH-camp5-Search

2021-11-08
193320
SEARCH9
camp5
Search
SEARCH-camp5-Search

2021-11-06
193320
SEARCH2
camp6
Search
SEARCH-camp6-Search

2021-11-07
193320
SEARCH1
camp7
Search
SEARCH-camp7-Search

2021-11-10
141542
SEARCH1
camp7
Search
SEARCH-camp7-Search




Date
Purchase_Order_ID
Membership_Number
Order_Value


2021-12-07
0088
183300
29.34

2021-12-18
0180
193320
132.51

2021-12-04
0050
141542
24.35


select * from
(
select *, RANK()OVER(partition by membership_number order by Event_Day) as rnk
from page_views as pv
INNER JOIN orders as o
ON pv.Member_ID=o.Membership_Number
) as q
where rnk=1


Event_Day
Member_ID
CV1
CV2
CV3
CV4
Date
Purchase_Order_ID
Membership_Number
Order_Value
rnk


2021-11-10
141542
SEARCH1
camp7
Search
SEARCH-camp7-Search
2021-12-04
0050
141542
24.35
1

2021-11-05
183300
APP
camp1
Trigger
APP-camp1-Trigger
2021-12-07
0088
183300
29.34
1

2021-11-04
193320
SEARCH
camp5
Search
SEARCH-camp5-Search
2021-12-18
0180
193320
132.51
1


select *
from 
(select pv.* 
from page_views as pv
inner join
       (
       select Member_ID, min(event_day) as mn_dt
       from page_views
       group by member_id
       ) as mn
   ON mn.Member_ID=pv.Member_ID and mn.mn_dt=pv.event_day
)as sq
INNER JOIN orders as o
ON sq.Member_ID=o.Membership_Number



Event_Day
Member_ID
CV1
CV2
CV3
CV4
Date
Purchase_Order_ID
Membership_Number
Order_Value


2021-11-10
141542
SEARCH1
camp7
Search
SEARCH-camp7-Search
2021-12-04
0050
141542
24.35

2021-11-05
183300
APP
camp1
Trigger
APP-camp1-Trigger
2021-12-07
0088
183300
29.34

2021-11-04
193320
SEARCH
camp5
Search
SEARCH-camp5-Search
2021-12-18
0180
193320
132.51

Event_Day	Member_ID	CV1	CV2	CV3	CV4
2021-11-05	183300	APP	camp1	Trigger	APP-camp1-Trigger
2021-11-14	183300	SEARCH	camp4	Search	SEARCH-camp4-Search
2021-11-04	193320	SEARCH	camp5	Search	SEARCH-camp5-Search
2021-11-08	193320	SEARCH9	camp5	Search	SEARCH-camp5-Search
2021-11-06	193320	SEARCH2	camp6	Search	SEARCH-camp6-Search
2021-11-07	193320	SEARCH1	camp7	Search	SEARCH-camp7-Search
2021-11-10	141542	SEARCH1	camp7	Search	SEARCH-camp7-Search

Date	Purchase_Order_ID	Membership_Number	Order_Value
2021-12-07	0088	183300	29.34
2021-12-18	0180	193320	132.51
2021-12-04	0050	141542	24.35