Using rank within a window function to filter results

user060355
user060355 Member
edited January 7 in Beast Mode

I am trying to analyze the initial purchase behavior of 2 subsets of users, mobile app users and non mobile app users.

I have client purchase history in a table where each row is a sale and contains whether the user is an app user or not and I've created a beast mode that uses variables "min sale date" and "max sale date" to filter the sales and determine the average spend per client.

First I calculate the total sales:

sum(CASE WHEN `Date` >= `Min. Sale Date` AND `Date` <= `Max. Sale Date` THEN
    	`Total`
    END
END)

Then I calcuate the number of clients:

COUNT(DISTINCT (CASE WHEN created_at_clients >= `Min. Sale Date` At AND created_at_clients <= `Max. Sale Date` THEN
    unique_mb_on_sale

END))

Then I simply divide the first by the second to achieve the avg. spend per client for the given date range.

I want to be able to filter this equation so that it only includes the client's first purchase so I did the following:

CASE WHEN Date >= Min. Sale Date AND Date <= Max. Sale Date THEN

RANK() over (PARTITION BY unique_mb_on_sale ORDER BY Date)

END

However when I try to filter based on this rank value it does not work. Somehow everything has a rank of 1 when plotting the avg. sale per client on the y-axis and app user vs. non app user on the x-axis. Is there any way around this?

Answers

  • MichelleH
    MichelleH Coach

    @user060355 Are you using Beast Mode or Magic ETL? If you are using Beast Mode, filtering by a rank will cause an error because their results are dynamically calculated in the card. This will have to be done in Magic ETL using a combination of a group by a constant (first two calculations) and a Rank and Window tile (third calculation). If you share some screenshots of how you currently have this set up I could give some more specific feedback.

  • user060355
    user060355 Member

    @MichelleH the issue there is that I want the rank to by dynamic based on the date range being filtered by the user. If I rank in the ETL, the date would be unknown. Is there a way around this or maybe a different filtering method to filter the minimum rank rather than rank = 1 since regardless of window, the lowest rank would always be the initial purchase?

Categories