Q:

calculate Average dead time by week by store

I have the following problem. I would like to calculate the average 'dead time' ( I mean time when there are no sales) by store and by week, but I don't know how to do it since I have very little knowledge about SQL. My data looks like this :
store    Item    local_created time   local_created_day      week
store1   chips    12:40:12              2019-05-20            2019023
store1   soda     13:25:12              2019-05-20            2019023
store1   coffee     13:56:12              2019-05-19          2019022
store2   paper     05:26:12              2019-05-20           2019023
store2   soda2     07:35:12              2019-05-20           2019023

And I would like to get something like this:
Week        Store        Avg dead time in the week
2019023     store1        45 min
2019023     store2        1 hour

How can I achieve this with just taking into account the first and last sale of the day (as if they were my opening and closing hours)?

A:

Below for BigQuery Standard SQL    
#standardSQL
SELECT week, store, AVG(minutes) AS avg_dead_time_in_minutes
FROM (
  SELECT store, week, 
    TIMESTAMP_DIFF(created, LAG(created) OVER(PARTITION BY week, store ORDER BY created), MINUTE) AS minutes
  FROM (
    SELECT store, week, 
      PARSE_TIMESTAMP('%F %T', CONCAT(local_created_day, ' ', local_created)) AS created
    FROM `project.dataset.table`
  )
)
GROUP BY week, store
HAVING NOT avg_dead_time_in_minutes IS NULL   

if to apply to sample data from your question - result is    
Row week    store   avg_dead_time_in_minutes     
1   2019023 store1  45.0     
2   2019023 store2  129.0    

