BiqQuery StandardSQL query to return Looker query history using Stitch Looker (v1) Integration (https://www.stitchdata.com/docs/integrations/saas/looker)

looker_query_history.sql

WITH
  history AS (
  SELECT
    *
  FROM (
    SELECT
      h.query_id,
      h.history_created_date AS query_ts,
      h.user_id AS user_id,
      h.history_query_run_count AS query_count,
      h.history_total_runtime AS query_runtime_secs,
      h.query_model AS query_model,
      h.query_view AS query_explore,
      h._sdc_batched_at AS query_history_sdc_batched_at,
      MAX(h._sdc_batched_at) OVER (PARTITION BY h.query_id, h.dims_hash_key, h.history_created_date ORDER BY h._sdc_batched_at) AS max_query_history_sdc_batched_at
    FROM
      `PROJECT.DATASET.query_history` h )
  WHERE
    query_history_sdc_batched_at = max_query_history_sdc_batched_at ),
  queries AS (
  SELECT
    *
  FROM (
    SELECT
      q.id,
      q.model,
      q.view AS explore,
      q.runtime,
      q.share_url,
      q._sdc_batched_at,
      q.client_id,
      q.slug,
      q.has_table_calculations,
      q.total,
      q.url,
      q._sdc_batched_at AS query_batched_at,
      MAX(q._sdc_batched_at) OVER (PARTITION BY q.id ORDER BY q._sdc_batched_at) AS max_query_sdc_batched_at
    FROM
      `PROJECT.DATASET.queries` q )
  WHERE
    query_batched_at = max_query_sdc_batched_at ),
  users AS (
  SELECT
    *
  FROM (
    SELECT
      u.id,
      u.email,
      u.display_name,
      u._sdc_batched_at AS user_sdc_batched_at,
      MAX(u._sdc_batched_at) OVER (PARTITION BY u.id ORDER BY u._sdc_batched_at) AS max_user_sdc_batched_at,
    FROM
      `PROJECT.DATASET.users` u )
  WHERE
    user_sdc_batched_at = max_user_sdc_batched_at ),
  explores AS (
  SELECT
    *
  FROM (
    SELECT
      e.view_name AS explore,
      e.project_name,
      e.connection_name,
      e.label AS explore_label,
      e.name AS explore_name,
      e._sdc_batched_at AS explore_sdc_batched_at,
      MAX(e._sdc_batched_at) OVER (PARTITION BY e.id ORDER BY e._sdc_batched_at) AS max_explore_sdc_batched_at
    FROM
      `PROJECT.DATASET.explores` e )
  WHERE
    explore_sdc_batched_at = max_explore_sdc_batched_at )
SELECT
  h.query_ts,
  h.user_id,
  u.email,
  u.display_name,
  h.query_count,
  h.query_runtime_secs,
  h.query_model,
  h.query_explore,
  q.id,
  q.model,
  q.explore AS explore,
  e.explore_label,
  e.connection_name,
  e.project_name,
  q.runtime,
  q.share_url,
  q._sdc_batched_at,
  q.client_id,
  q.slug,
  q.has_table_calculations,
  q.total,
  q.url,
FROM
  history h
JOIN
  queries q
ON
  h.query_id = q.id
LEFT JOIN
  users u
ON
  h.user_id = u.id
LEFT JOIN
  explores e
ON
  h.query_explore = e.explore