fbpx

Continuous Values

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

 

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…