BigQuery - Run Query per Table

Question

Big Query allows you to separate tables via timestamp using a table_query. Here's an example query we are using:

SELECT
  COUNT(*) as num
FROM (
  SELECT
    dsn,
    SUM(hours_used) AS total_hours
  FROM
    [device_use.day20150901] // Now - 1 day
  WHERE
    nest_state > 5
  GROUP BY
    dsn )
WHERE
  total_hours > 1

Now what we WANT to do, is run the same query, but on a PER TABLE DAY basis. IE, [device_use.day20150901], [device_use.day20150902], [device_use.day20150903], etc.

Is this possible, or do I have to make a separate table to do this single query.

Thanks!

Answer 1

Just added few things to your original query, assuming existence of timestamp field being within respective table's day

SELECT
  day,                                          -- added
  COUNT(*) as num
FROM (
  SELECT
    DATE(your_timestamp_field) as day,          -- added
    dsn,
    SUM(hours_used) AS total_hours
  FROM
     (TABLE_DATE_RANGE(device_use.day,          -- added
                    TIMESTAMP('2015-09-01'),    -- added
                    TIMESTAMP('2015-09-29')))   -- added
  WHERE
    nest_state > 5
  GROUP BY
    1,                                          -- added
    2 )
WHERE
  total_hours > 1