CODEWITHSUNDEEP
sqlvertica
d.tang
d.tang

Reputation: 37

Find most recent date of purchase in user day table

I'm trying to put together a query that will fetch the date, purchase amount, and number of transactions of the last time each user made a purchase. I am pulling from a user day table that contains a row for each time a user does anything in the app, purchase or not. Basically all I am trying to get is the most recent date in which the number of transactions field was greater than zero. The below query returns all days of purchase made by a particular user when all I'm looking for is the last purchase so just the 1st row shown in the attached screenshot is what I am trying to get.

screen shot of query and result set

select tuid, max(event_day), 
purchases_day_rev as last_dop_rev, 
purchases_day_num as last_dop_quantity,  
purchases_day_rev/nullif(purchases_day_num,0) as last_dop_spend_pp
from 
(select tuid, event_day,purchases_day_rev,purchases_day_num 
from
app.user_day
where purchases_day_num > 0
and tuid='122d665e-1d71-4319-bb0d-05c7f37a28b0'
group by 1,2,3,4) a
group by 1,3,4,5

Upvotes: 0

Views: 75

Answers (2)

mauro
mauro

Reputation: 5950

I'm not going to comment on the logic of your query... if all you want is the first row of your result set, you can try:

<your query here> ORDER BY 2 DESC LIMIT 1 ;

Where ORDER BY 2 DESC orders the result set on max(event_day) and LIMIT 1 extracts only the first row.

Upvotes: 1

M. Wise
M. Wise

Reputation: 186

I don't know all of the ins and outs of your data, but I don't understand why you are grouping within the subquery without any aggregate function (sum, average, min, max, etc). With that said, I would try something like this:

select tuid
,event_day
,purchases_day_rev as last_dop_rev
,purchases_day_num as last_dop_quantity
,purchases_day_rev/nullif(purchases_day_num,0) as last_day_spend_pp
from app.user_day a
inner join
(
select tuid
,max(event_day) as MAX_DAY
from app.user_day
where purchases_day_num > 0
and tuid='122d665e-1d71-4319-bb0d-05c7f37a28b0'
group by 1
) b
on a.tuid = b.tuid
and a.event_day = b.max_day;

Upvotes: 0

Related Questions