Calculating if it is the first occurrence of an event

Question & Answer
,
1

Hi all,

I am trying to get if a particular transaction type is the first in time for that type and user, but I am not managing. I tried using firstValue (and also minIf), but it is not working as expected and the documentation is scarce, to say the least.

This is my table:

PK_LOG_ID CREATED_AT USER_ID TRANSACTION_TYPE AMOUNT
0001 2022-01-01 10:00:01 123 Buy 10
0002 2022-01-01 14:10:12 123 Sell 20
0003 2022-01-01 17:20:00 123 Buy 80
0004 2022-01-01 20:00:20 456 Sell 35
0005 2022-01-01 20:20:20 456 Sell 35
0006 2022-01-02 09:00:35 456 Buy 90
0007 2022-01-03 11:02:11 123 Buy 40
0008 2022-01-04 10:00:01 123 Buy 10

What I would like is to have a flag, 1 or 0, showing if the transaction is the first of that type for that particular user.
So something like:

PK_LOG_ID CREATED_AT USER_ID TRANSACTION_TYPE AMOUNT FIRST_OCCURRENCE
0001 2022-01-01 10:00:01 123 Buy 10 1
0002 2022-01-01 14:10:12 123 Sell 20 1
0003 2022-01-01 17:20:00 123 Buy 80 0
0004 2022-01-01 20:00:20 456 Sell 35 1
0005 2022-01-01 20:20:20 456 Sell 35 0
0006 2022-01-02 09:00:35 456 Buy 90 1
0007 2022-01-03 11:02:11 123 Buy 40 0
0008 2022-01-04 10:00:01 123 Buy 10 0

What I am mostly interested is into finding the first transaction for the Buy type, but this would work as well. Worst case scenario I could to having the date of the first Buy transaction in the last column.
I tried minIf and mostly firstValue, but I cannot wrap my head around the way of making it work.
I was expecting that something like this for example would return the first transaction time:

firstValue({CREATED_AT}, [{CREATED_AT} ASC], [{TRANSACTION_TYPE}, {USER_ID}, {CREATED_AT}])

But it does not work.

Having such a value would allow me to check the unique transactions and amount for first time buyers in a specified time range.

Thank you!

2

You can try this:

TRANSACTION_NUMBER = rank([{CREATED_AT} ASC], [{USER_ID}, {TRANSACTION_TYPE}], PRE_AGG)

image
image732×239 16 KB

Then filter by TRANSACTION_TYPE = “Buy” and TRANSACTION_NUMBER = 1.

3 Likes
3

Hello @David_Wong,
Thank you for your help, and sorry for the late reply but it’s been crazy at work.
That is close enough to what I am looking for (as the ranks works per aggregation, not overall), and I could tweak it for my needs, even though I resolved by importing the information using SQL.
And as I am learning to use level aware functions, that helps a lot to learn :slight_smile:

Thank you!