Data Mart Samples With SQL Snippets

Global weather patterns: Understand the world's weather dynamics

Focusing on global weather patterns using the dataset bigquery-public-data.noaa_gsod.gsod2024, we'll retrieve average temperature readings and average humidity for different cities in 2024.

This Data Mart assumes that stn represents stations that can be mapped to specific cities and temp and dew are used to calculate the average temperature and average dew point (as a proxy for humidity), respectively.

SELECT
  stn AS station_code,
  AVG(temp) AS average_temperature,
  AVG(dewp) AS average_humidity
FROM
  `bigquery-public-data.noaa_gsod.gsod2024`
WHERE
  temp != 9999.9 AND -- Excluding placeholder values for missing data
  dewp != 9999.9
GROUP BY
  station_code
ORDER BY
  average_temperature DESC
LIMIT
  10;

 

Website Performance Analysis: Dive into page metrics

Unlock insights about website's pages performance using GA4 BigQuery Export, along with our Data Mart example based on a public dataset: bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*.

It’s perfect for marketers and web analysts, as well as for those who work with marketing reports and it delves into page views, user engagement, and interaction depth to enhance content strategy, optimize user experience, and inform data-driven decisions.

SELECT
  event_date,
  page_location,
  page_title,
  COUNTIF(event_name = 'page_view') AS page_views,
  COUNT(DISTINCT user_pseudo_id) AS users,
  SAFE_DIVIDE(COUNTIF(event_name = 'page_view'), COUNT(DISTINCT user_pseudo_id)) AS pageviews_per_user,
  SUM(IF(event_name = 'user_engagement', engagement_time, 0)) AS total_engagement_time,
  AVG(IF(event_name = 'user_engagement', engagement_time, NULL)) AS avg_engagement_time
-- For Unique Pageviews and Engagements per User, consider calculating these based on distinct user interactions within subqueries if necessary
FROM (
  SELECT
    event_date,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
    event_name,
    IF(event_name = 'user_engagement', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') / 1000, 0) AS engagement_time
  FROM
--replace next line with your project_id.dataset_id if running in the BigQuery interface
    `{gcp_project_id default="bigquery-public-data"}.{dataset_id default="ga4_obfuscated_sample_ecommerce"}.events_*`
  WHERE
--replace next line with your dates if running in the BigQuery interface
    _TABLE_SUFFIX BETWEEN '{start_date default="20210101" type="input"}' AND '{end_date default="20210110" type="input"}'
    AND (event_name = 'page_view' OR event_name = 'user_engagement')
)
GROUP BY
  event_date, page_location, page_title
ORDER BY
  event_date, page_views DESC

 

Tech Language Trends: A Snapshot of Developer Activity on GitHub

Dive into the dynamic world of technology through the "Tech Language Trends" Data Mart, designed to illuminate the ebbs and flows of developer activity across various programming languages.

By analyzing commit data from GitHub's comprehensive repositories, this Data Mart offers a unique perspective on which languages are leading the charge in innovation and usage in the tech industry.

Ideal for tech analysts, industry watchers, and anyone curious about the programming languages shaping our digital future.

SELECT
 FORMAT_TIMESTAMP('%Y-%m', committer.date) AS month,
 COUNT(*) AS commits
FROM
 `bigquery-public-data.github_repos.sample_commits`
WHERE
 committer.date IS NOT NULL
GROUP BY
 month
ORDER BY
 month ASC

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.