Count days since 3rd event from today

Question

I have a table Events in LibreOffice Base with a Firebird database (version 3.0.8) that records how many times an event occurs. Example below:

Date	EventCount
22-04-01	15
22-09-30	10
22-10-01	1
22-10-04	1

I would like to create a query to output the number of days from today since the 3rd event occurred. In the example above, the third event to date would be 22-09-30.

I assume the code would look something like:

SELECT "Date"
WHERE DATEDIFF(DAY, CURRENT_DATE, DATE '30-09-2022') AS "Third Last Event"
FROM "Events"

However, DATE '30-09-2022' is not a fixed value. I am just using it as an example of what the third event would be in the above example's case. Given that new rows would be added to this table and more values would be added to EventCount, it would change on a regular basis.

What would I have to replace DATE '30-09-2022' with, so that I could run the query and have it return the value in the Date column that corresponds with the third EventCount from CURRENT_DATE?

Answer 1

You can use the SUM window function to calculate a running total, and then find which row has the first equal or higher running total. Then you can use datediff (in my example I switched the position of current_date because I liked it better for the name I gave my column - days_ago):

select 
  event_date,
  event_count,
  event_count_running_total,
  datediff(day, event_date, current_date) days_ago
from (
  select 
    event_date, 
    event_count, 
    sum(event_count) over (order by event_date desc) event_count_running_total
  from events
)
where event_count_running_total >= 3
order by event_date desc
fetch first row only

https://dbfiddle.uk/bGwQtI2v

With Firebird 4.0, using window frames would allow for a (slightly) different solution:

select 
  event_date,
  event_count,
  event_count_running_total,
  event_count_running_prev,
  datediff(day, event_date, current_date) days_ago
from (
  select 
    event_date, 
    event_count, 
    sum(event_count) over (order by event_date desc) event_count_running_total, 
    sum(event_count) over (order by event_date desc rows between unbounded preceding and 1 preceding) event_count_running_prev
  from events
)
where 3 between event_count_running_prev and event_count_running_total

https://dbfiddle.uk/r9q0nmHj