Finding the earliest purchase place for each unique user based

Question

I need to get the userID and the marketplace, where the user first bought something.

The db looks like this:

userID, marketplace, dateOfOrder
74526,  Amazon,      2/1/2021
74526,  eBay,        1/1/2021
74526,  Walmart,     12/10/2021 
74525,  Amazon,      14/5/2021
74525,  eBay,        12/4/2021
74525,  Walmart,     9/10/2021

I need to get the following:

userID, marketplace
74526,  eBay
74525,  eBay

I tried several ways but neither is returning the result without multiple issues or increased complexity, and I am trying to keep the query as little as possible for whoever will maintain it in the future. Is there an easy way to achieve what I am looking for?

What if the user on first time bought from more than one market? — Hamlet Hakobyan, Nov 29 '21 at 15:41
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Nov 29 '21 at 15:48

score 3 · Accepted Answer · edited Nov 29 '21 at 15:44

You can use the ROW_NUMBER function to get the "first bought" row of each user. Add row numbering with:

row_number() over(partition by userID order by dateOfOrder asc) as r

and then extract where r=1 (first row = first bought based on date ordered ascending):

select userID, marketplace
from (
  select *, row_number() over(partition by userID order by dateOfOrder asc) as r
  from your_table
)
where r=1

score 0 · Answer 2 · answered Nov 29 '21 at 15:42

0

Sql Server features a nifty top(1) with ties .. order by for that

select top(1) with ties t.* 
from your_table t
order by row_number() over(partition by userid order by date);

answered Nov 29 '21 at 15:42

Serg

22,285
5
21
48

Finding the earliest purchase place for each unique user based

2 Answers2