Another interesting SQL question one of my students has sent me recently. This question tests your understanding of SQL analytic fucntions. A bank company has 3 branches in Tel Aviv. In every branch there are 2 ATMs. The following table describes the periods of time in which these ATMs were active.

 

continuous data_sql_question

 

As you can see –

  • The ATMs of branch number 1 were active between 08:30 and 09:55 AM (without any non-active period).
  • The ATMs of branch number 2 were active between 09:20 and 09:25 AM. Then after a non-active period of 5 minutes, they were active from 09:30 to 9:55 AM. Finally, after another non-active period of 35 minutes, they were active between 10:30 and 11:55 AM.
  • The ATMs of branch number 3 were active between 08:00 to 09:50 AM, after a non-active period of 90 minutes, they were active from 11:20 to 12:15 AM.

Write a query that will retrieve the continuous time periods in which these ATMs were active.  This query should retrieve:

  • 1 row for the first bank – 08:30-09:55
  • 3 rows for the second bank – 09:20-09:25, 09:30-09:55, 10:30-11:55.
  • 2 rows for the third bank – 08:00-09:50, 11:20-12:50.

 

image