Q: List with dates, how to find the second, third, ........ ranked score? 🤔

1

As you can see in the doc embedded, I have found the from the list with dates the biggest streak.

Q: How can I find the second, third,… ranked score?

2

Hi @Jean_Pierre_Traets :slight_smile:
I’m not sure i’ve understood correctly your “problem”, surely i’ve done similiar algorithms in the past and those have made me crazy, literally, dates in coda do what they want, and sorted elements it’s not always sorted as one could imagine :joy:
Can you please try to explain better the situation? I’ve got the “days in a row” and the bigger is higher principle, so thanks to that you know which one is the first, but you also know what are the other in order or not?
In that case if the table upon it’s valid i think it’s easy to recover the other value :slight_smile:
Let me know, this sound interesting :grinning:

3

Dear @Mario, thanks for your time and effort to support.

The first longst streak is 22 days, that’s rather simple, as you mentioned.
My challenge is how to calculate the second, third longest streak with a formula, in the “streak table”, I have put the results manualy to show what result I’am expecting.

I am going to check if I can find an open template with a game, where scores for each user are calculated and there should also be a ranking board.

Not sure if I am right, but “something say me”, each streak should get an independ indentifier, to show that they are belonging to one and the same group and then I think it will become more easy to rank them by this indentifier :thinking:

4

I added the logic @Jean_Pierre_Traets.

Two parts of the solution:

  1. Figure out which rows are the end of a streak (so that when you’re sorting by cumulative days, you’re excluding the days that are just ongoing streaks). In my formula it’s a day where the next date has 0 as cumulative score, or doesn’t exist yet (to count in the ongoing streak that’s happening right now)

  2. Then it’s easy to filter, sort, and take the .Nth() item

1 Like
5

P.S.

This is a great statement that allows me to share one more piece of problem solving knowledge.

Which is, ask yourself, “Do I actually need to calculate all this?”
And if not, “Okay, what is the simplest thing I actually need to calculate here?”

Let’s say you decided to assign each streak an identifier. And that’s something that you may have actually wanted to do, e.g. if you wanted to color-code each streak group or something. You’d probably have to introduce a separate table of Streaks with date bounds, or write a very convoluted formula. You could then probably calculate the Max() from each group and there have your numbers. That would be a computationally complex solution.

But in your case, only the final numbers per streak matter. So do you really care whether this or that date belongs to a certain streak? No, you only care that you know the “final” numbers of each streak (since they’re already Max by nature). So it’s a good idea to think whether you can simply filter your subset down to only those rows/numbers. You know that when a new streak starts, it starts counting from zero. So you can write a formula that checks whether the next row is zero — this means this one is the end of a streak.

Another example.
Say, you want to zebra-stripe your streaks (odd streak with white background, even streak with grayish background, to tell those apart).

Do you need to assign each row some group reference?

Yes, although you don’t need a reference but simply a number (e.g. group 1, 2, 3, 4…)

How can you calculate that? Do you still need to create a Streaks table and then calculate an index of each Streak, and then look it up?

You could do that… but also you could just calculate how many “is end of streak” checkboxes are there before this current row :slight_smile:

thisTable.Filter(
  [Is end of streak]
  AND CurrentValue.Date < thisRow.Date
).Count()

image
image619Ă—813 9.25 KB

This approach is a staple to any algorithm programming. Learning to think of the simplest operations needed is key to writing effective calculations (and also passing coding interviews). Of course you still need to know how each small operation behaves and what implicit computational complexity it holds.

Perhaps, actually making a Streaks table would be a more efficient solution, computationally speaking. It would come with its own downside though: the need to maintain that table and add new rows whenever a new streak is happening.

Read / watch some videos on asymptotic complexity to get what I mean.

1 Like
6

Dear @Paul_Danyliuk,

Your solution’s simplicity, logic thinking and identifying steps towards the solution are amazing like always Paul

Just one small thing, the first day (0) should be added to get the right amount of days in the streak.

Thank you / благодаря :clap: