9. Devices

Use the SQL code below and change {{projectId}} and {{datasetName}} to your own.

#Preparing Devices object

WITH
  input AS
    (
      SELECT
        PARSE_DATE('%Y%m%d','20220901') AS date_start,
        PARSE_DATE('%Y%m%d','20220930') AS date_end
    )

SELECT
    CAST(FARM_FINGERPRINT(CONCAT( --deviceHash
          IFNULL(deviceCategory, ''),
          IFNULL(operatingSystem, ''),
          IFNULL(operatingSystemInfo, ''),
          IFNULL(browser, ''),
          IFNULL(browserVersion , '')
        )) AS STRING) AS deviceHash,
    deviceCategory,
    operatingSystem,
    operatingSystemInfo,
    browser,
    browserVersion 
FROM
  input AS i, `{{projectId}}.{{datasetName}}._temp_sessions`
WHERE
  date BETWEEN i.date_start AND i.date_end
GROUP BY 1,2,3,4,5,6

You will get the data in the table 'devices' with the schema below: