SQLize | PHPize | SQLtest

create table sales ( card_id varchar(10) ,saled_date date ) ;insert into sales (card_id, saled_date) values ('card-1', '2021-02-15') ,('card-1', '2022-03-05') ,('card-1', '2022-03-27') ,('card-1', '2022-05-18') ,('card-1', '2023-06-10') ,('card-2', '2021-01-15') ,('card-2', '2022-05-18') ,('card-2', '2023-06-21') ,('card-3', '2022-05-20') ,('card-4', '2022-05-20') ,('card-5', '2022-05-20') ;create table activations ( card_id varchar(10) ,activity_date date ) ;insert into activations (card_id, activity_date) values ('card-1', '2021-02-20') ,('card-1', '2023-07-17') ,('card-2', '2022-05-25') ,('card-3', '2022-08-15') ,('card-5', '2023-01-15') ;CREATE TEMPORARY TABLE t --;drop table if exists t select s.card_id ,s.saled_date ,isnull(dateadd(day, - 1, lead(s.saled_date) over (partition by s.card_id order by s.saled_date)), '2099-12-31') [prev_date] into #t from sales s select t.card_id ,t.saled_date --,t.prev_date ,a.activity_date from #t t left join activations a on a.card_id = t.card_id and a.activity_date >= t.saled_date and a.activity_date <= t.prev_date