Query for pvs of a page daily - Bigquery - google analytics

Question

I have connected analytics with bigquery. What query should i type if i want to get daily pageviews (for each page), from the beginning of the year?

page       pv    date
/mysite1   5     01-01-2017
/mysite    2     01-01-2017

and so on? Could you help with this?

Answer 1

These queries match the results in my GA account, I welcome any feedback for a better approach!

Standard SQL:

SELECT
  date,
  hits.page.pagePath AS pagePath,
  COUNT(*) AS pageviews
FROM
  `ga_sessions_*`, --update
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '20170101'  --start date
  AND '20170101'                    --end date
  AND hits.type = 'PAGE'
GROUP BY
  date,
  pagePath
ORDER BY
  date ASC,
  pageviews DESC

Legacy SQL:

SELECT
  date,
  hits.page.pagePath AS pagePath,
  COUNT(*) AS pageviews
FROM
  TABLE_DATE_RANGE([ga_sessions_], -- update
    TIMESTAMP('2017-01-01'),  -- start date
    TIMESTAMP('2017-01-01')   -- end date
  )
WHERE
  hits.type = 'PAGE'
GROUP BY
  date,
  pagePath
ORDER BY
  date ASC,
  pageviews DESC