The row order is mentioned in the description:

The output should List user IDs (user_id) and the count of their bad katas (bad_kata_count). And be ordered first by the count of bad katas in descending order. In case of a tie - by user ID in descending order.

It was a little frustrating because I wasn't sure about the expected order of the results. A hint about the expected sorting order in the description would be very helpful.

the root cause of the issue is that the customer table is useless to solve the problem; all the necessary information is stored in rental. I removed the schema of customer from the description.

I raised a better issue there with the cause

As noted by several comments below, the reference solution disagrees with the description about how to implement the 20% cutoff.

The description specifies to round up (i.e. ceil), but the reference solution uses percent_rank() < 0.2. As noted by Unnamed, this disagrees with ceil() when the number of unique customers is congruent to 1 modulo 5.

For example, for 551 customers, the reference solutions counts 110 top customers but ceil(551 * 20%) = ceil(110.2) = 111.

This means that most solutions only pass when the random tests contain a number of unique customers that is not ≡ 1 (mod 5).

fixed. I added a tie-break by category name to the description, reference solution, and author's solution.

Good one, straightforward!

The kata is ok, but most likely I would rate it as 5 kyu rather than 6. Spent a few hours to solve it

it's probably too late to change this now, so I added the de-facto requirement to the description (right-padding to 9 spaces, because that's what to_char(, 'Day') happens to return)

I amended the description to specify the expected order.

The emphasis on gaps in the description feels like a red herring. We don't need to care about gaps at all, we can treat the input as a discrete time series with no duplicate dates and it is irrelevant to our query whether there is one day or more between two consecutive entries, as long as we order them chronologically. The concept of a gap adds nothing to the task, as we never need to treat them in a particular way.

bornForThis, slop-canon rehab for devs in 2026
thank you!

I have found diferences in output of my wrong script and script of user Twilight_Sun (which resolved this excercise) on my local test db. Diference is for example in user with id 17:
My script output:

+-----+---------+---------+------------+--------+-------------------------+-------------------------------------+ | id | user_id | country | date | amount | days_to_reach_threshold | avg_country_days_to_reach_threshold | +-----+---------+---------+------------+--------+-------------------------+-------------------------------------+ | 697 | NULL | NULL | 2023-02-28 | 10 | NULL | NULL | | 704 | NULL | NULL | 2023-02-28 | 1 | NULL | NULL | | 705 | 17 | Spain | 2023-03-29 | 10 | 29 | 46 | +-----+---------+---------+------------+--------+-------------------------+-------------------------------------+

output of Twilight_Sun's script:
+-----+---------+---------+------------+--------+-------------------------+-------------------------------------+ | id | user_id | country | date | amount | days_to_reach_threshold | avg_country_days_to_reach_threshold | +-----+---------+---------+------------+--------+-------------------------+-------------------------------------+ | 697 | NULL | NULL | 2023-02-28 | 10 | NULL | NULL | | 698 | NULL | NULL | 2023-03-30 | 2 | NULL | NULL | | 699 | 17 | Spain | 2023-04-17 | 8 | 48 | 48 | +-----+---------+---------+------------+--------+-------------------------+-------------------------------------+

For referenca data in my local db:
select * from transactions where user_id = 17 order by date > results in: +-----+---------+---------+------------+--------+ | id | user_id | country | date | amount | +-----+---------+---------+------------+--------+ | 697 | 17 | Spain | 2023-02-28 | 10 | | 704 | 17 | Spain | 2023-02-28 | 1 | | 705 | 17 | Spain | 2023-03-29 | 10 | | 698 | 17 | Spain | 2023-03-30 | 2 | | 699 | 17 | Spain | 2023-04-17 | 8 | | 706 | 17 | Spain | 2023-04-27 | 8 | | 700 | 17 | Spain | 2023-05-07 | 4 | +-----+---------+---------+------------+--------+

So which output is correct?

glad that they taught you much ^^