CODEWITHSUNDEEP
sqlrank
ChLi
ChLi

Reputation: 9

find the earliest record

I am searching for a long time on the net. But no use. Please help or try to give some ideas on how to achieve this. There are 3 tables there and my target is to find the month, the week, and the customer id for customers' first purchase. The customer might have multiple purchases on their first shopping day and I want to get the earliest one. Below are the tables and my try. The problem with my code is that it still output duplicate customer ids. I think maybe some customers shop at different stores on their first purchase day. Thanks in advance.

cust
cust_id  |  first_purchase  |  
001      |   20200107     
002      |   20200109 
003      |   20200102 
004      |   20200103 

date
datetime |  week  |  month |   year
20200107 |   2    |   1    |   2020    |     
20200103 |   1    |   1    |   2020    | 
20200102 |   1    |   1    |   2020    | 
20200101 |   1    |   1    |   2020    |


sale
cust_id  |  store_id  |  time  |   datetime |  
001      |   2342     |  9:00  |   20200107     
002      |   2345     |  8:00  |   20200107     
003      |   6234     |  2:00  |   20200107     
004      |   4533     |  3:00  |   20200107     

Select month, week, cust_id from cust
inner join date on cust. first_purchase= date.datetime
left join 
(select distinct cust_id, store_id,min(datetime),min(time) 
from sale group by cust_id,store_id) 
as bdate 
on cust.cust_id=bdate.cust_id
WHERE year ='2020' and first_purchase IS NOT NULL and MONTH=1 and week=1

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Hmmm . . . You can use row_number():

select s.*, d.*
from (select s.*,
             row_number() over (partition by cust_id order by datetime, time) as seqnum
      from sales s
     ) s join
     date d
     on s.datetime = d.datetime and seqnum = 1;

Upvotes: 1

Related Questions