Group SQL data and split into columns-CodePudding

I have a table with the following columns, where the timestamp column indicates the date the user viewed a product, and the purchase column if the view generated a purchase:

id	userId	productId	timestamp	purchase
1	2	4	2022-01-07	0
2	2	4	2022-01-10	1
3	2	4	2022-01-12	0
4	2	4	2022-01-16	1

How to group the data by userId and productId in order to return as follows:

id	userId	productId	firstView	lastView	firstPurchase
1	2	4	2022-01-07	2022-01-16	2022-01-10

For the firstView and listView columns I know I should use Min() and Max(), but the firstPurchase column I can't display. I tried using CASE when column purchase = 1 but it didn't work.

CodePudding user response：

You need to use MIN(CASE WHEN purchase = 1 THEN "timestamp" ELSE NULL END), like this:

create table your_table (id int, userId int, productId int, "timestamp" date, purchase int)

insert into your_table (id, userId, productId, "timestamp", purchase)
values
(1,2,4,'2022-01-07 00:00:00',0),
(2,2,4,'2022-01-10 00:00:00',1),
(3,2,4,'2022-01-12 00:00:00',0),
(4,2,4,'2022-01-16 00:00:00',1);


SELECT  
       userId, 
       productId, 
       MIN(timestamp) AS firstView, 
       MAX(timestamp) AS lastView, 
       MIN(CASE WHEN purchase = 1 THEN "timestamp" ELSE NULL END) AS firstPurchase
FROM your_table
GROUP BY 
         userId,
         productId;

p.s. please don't call a column "timestamp" :)

CodePudding user response：

Try this:

SELECT t1.id, t1.userId, t1.productId, t1.timestamp,
(SELECT t3.timestamp
FROM yourtable t3
WHERE t3.userId = t1.userId
AND t3.productId = t1.productId
AND NOT EXISTS
    (SELECT 1
    FROM yourtable t4
    WHERE t4.userId = t3.userId
    AND t4.productId = t3.productId
    AND t4.timestamp > t3.timestamp
    )
) as lastView,
(SELECT t3.timestamp
FROM yourtable t3
WHERE t3.userId = t1.userId
AND t3.productId = t1.productId
AND t3.purchase = 1
AND NOT EXISTS
    (SELECT 1
    FROM yourtable t4
    WHERE t4.userId = t3.userId
    AND t4.productId = t3.productId
    AND t4.timestamp < t3.timestamp
    AND t4.purchase = 1
    )
) as firstPurchase
FROM yourtable t1
WHERE NOT EXISTS
    (SELECT 1 FROM yourtable t2
    WHERE t1.userId = t2.userId
    AND t1.productId = t2.productId
    AND t2.timestamp < t1.timestamp)

See Sql Fiddle