CODEWITHSUNDEEP
sqlsql-server
Roland Simandiev
Roland Simandiev

Reputation: 3

For each product get the second customer who made a purchase

Given a table:

CustomerID  InvoiceID   ProductID   Date    Income

1            1234551      A       1/1/2015   300
1            1234552      A       1/2/2016   300
2            1234553      B       1/3/2016   500
3            1234554      C       1/4/2016   400
4            1234555      A       1/5/2015   300
4            1234555      B       1/5/2015   500
3            1234554      C       1/4/2016   400
3            1234556      A       1/8/2016   300
3            1234556      B       1/8/2016   500
2            1234553      A       1/3/2016   300
3            1234557      C       1/11/2016  400
4            1234561      D       1/12/2016  1200
5            1234565      S       1/13/2016  1800
6            1234569      A       1/14/2016  300
7            1234573      B       1/15/2016  500
8            1234577      C       1/16/2016  400
9            1234581      A       1/17/2016  300
10           1234585      C       1/18/2016  400
11           1234589      B       1/19/2015  500
12           1234593      C       1/20/2016  400
13           1234597      D       1/21/2016  1200
14           1234601      G       1/22/2016  700
15           1234605      A       2/23/2016  300

Question: For each product get the second customer who made a purchase

The following query

With cte_B
AS
(select  [ProductID],CustomersID,date
,DENSE_RANK() over (partition by [ProductID] order by date asc) AS Second_Customer
from Bright_Data
)
Select *
from cte_B
where Second_Customer = 2

Gives the result:

ProductID   CustomersID date    Second_Customer
A               4      2015-01-05   2
B               11     2015-01-19   2
C               3      2016-01-11   2
D               13     2016-01-21   2

And the expect result is:

ProductID   CustomersID date    Second_Customer
A               4      2015-01-05   2
B               11     2015-01-19   2
C               **8**      2016-01-11   2
D               13     2016-01-21   2

Here is the script to create a table:

Create table Table_A
( CustomersID int NULL,
  InvoiceID    int NULL,
  ProductID nvarchar(1) NULL,
  [Date] date NULL,
  Income int NULL )

  go

  insert into Table_A
values
(1,1234551,'A','2015-01-01',300),
(1,1234552,'A','2016-01-02',300),
(2,1234553,'B','2016-01-03',500),
(3,1234554,'C','2016-01-04',400),
(4,1234555,'A','2015-01-05',300),
(4,1234555,'B','2015-01-05',500),
(3,1234554,'C','2016-01-04',400),
(3,1234556,'A','2016-01-08',300),
(3,1234556,'B','2016-01-08',500),
(2,1234553,'A','2016-01-03',300),
(3,1234557,'C','2016-01-11',400),
(4,1234561,'D','2016-01-12',1200),
(5,1234565,'S','2016-01-13',1800),
(6,1234569,'A','2016-01-14',300),
(7,1234573,'B','2016-01-15',500),
(8,1234577,'C','2016-01-16',400),
(9,1234581,'A','2016-01-17',300),
(10,1234585,'C','2016-01-18',400),
(11,1234589,'B','2015-01-19',500),
(12,1234593,'C','2016-01-20',400),
(13,1234597,'D','2016-01-21',1200),
(14,1234601,'G','2016-01-22',700),
(15,1234605,'A','2016-02-23',300)

Upvotes: 0

Views: 319

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need to pair down the customers first so there is only one record per customer:

select bd.*
from (select customerId, productId, min(date) as first_date,
             row_number() over (partition by productId order by min(date)) as seqnum
      from bright_data bd
      group by customerId, productId
     ) bd
where seqnum = 2;

Upvotes: 3

Related Questions