You can't use the word group, even in comments.

there is some kind of error with the kata. I solved the kata without using GROUP BY but it will not let me pass and it's reasoning is that I used GROUP BY, which I did not

It is unclear why in the described example "Annabelle Lee" is not included in the result, it has 1 record where the function returns the distance from 1 to 3

I managed to run the query without getting the error but it still seems like there is some strange behaviour going on. I pass all the tests except for the last random one (Find Inventory Changes (Randomized Data) validates the query against multiple random date combinations.

In general, I think you were on the right track with the nulls - returning a NULL generates the initial error.

Another possibility is that you return no rows (i.e. an empty result set) when there should be some.

Sounds good and thanks for the quick response. As far as I can tell, nulls are not being returned but I'll give it another look in a little bit. To add some info, this what comes back from the backtrace:
./spec/query_spec.rb:168:in map' ./spec/query_spec.rb:168:in format_output'
./spec/query_spec.rb:158:in block (3 levels) in <top (required)>' ./spec/query_spec.rb:127:in times'
./spec/query_spec.rb:127:in block (2 levels) in <top (required)>' ./spec/query_spec.rb:6:in block (3 levels) in <top (required)>'
/usr/local/bundle/gems/sequel-5.47.0/lib/sequel/database/transactions.rb:251:in _transaction' /usr/local/bundle/gems/sequel-5.47.0/lib/sequel/database/transactions.rb:233:in block in transaction'
/usr/local/bundle/gems/sequel-5.47.0/lib/sequel/connection_pool/threaded.rb:92:in hold' /usr/local/bundle/gems/sequel-5.47.0/lib/sequel/database/connecting.rb:269:in synchronize'
/usr/local/bundle/gems/sequel-5.47.0/lib/sequel/database/transactions.rb:195:in transaction' ./spec/query_spec.rb:5:in block (2 levels) in <top (required)>'

One reason for this error could be that you returned NULL in a column which expects a value. Make sure your query returns no NULLs and try again.

Nonetheless, the error sohuld be fixed, because tests should not crash like that on unexpected values.

I can't complete as I'm getting this error: ./spec/query_spec.rb:169:in block in format_output': undefined method ljust' for nil:NilClass (NoMethodError)
Any idea what is going on?

Tests do not require a final dot.

Expected values

film_id	film_title	                            rental_count	    last_rental_date
459	    Indiana Jones and the Last Crusade (R)    13	              December 12, 2024
188	    Cinema Paradiso (G)	                   13	              December 01, 2024
304	    The Shining (R)	                       13	              December 01, 2024
295	    All About Eve (PG)	                    12	              November 11, 2024
171	    The Kid (PG)	                          12	              December 12, 2024
300	    The Great Dictator (PG)	               12	              December 09, 2024
145	    The Help (PG-13)	                      12	              December 04, 2024

2nd level sorting order is to be the rental date, so I think this should better be sorted be the actual dates, not the strings, otherwise Nov 11th will be shown as "later" than Dec 12th.

Sometimes I think: Quick, quick, hit submit before the tests decide otherwise again.

Ensure data integrity:

1. Avoid overlaps:

   • For each customer, there should be no overlapping time periods within any single table.
   • Overlapping records can never be justified and must be eliminated.

2. Handle gaps:

   • Gaps in the timeline are permissible.
   • However, given the use of both 1 and 0 as status indicators, aim for continuity.
     • Each customer should have a continuous period of records available in both tables.
     • This continuous period may be segmented into different status periods (1 or 0).

Good night

sorry, indeed looks that it is ambiguous :/ Putting kata in draft and on the fresh head will polish tests. Good night :)

This makes no sense to me: employee_id 1 in sample test 2 has ambiguous coverage_flag between 2024-02-10 and 2024-02-15 :/

{
    employee_id:     1,
    coverage_start:  '2024-01-01',
    coverage_end:    '2024-02-15',
    coverage_flag:   1
  },
  {
    employee_id:     1,
    coverage_start:  '2024-02-10',
    coverage_end:    '2024-03-01',
    coverage_flag:   0
  },